Conditional formatting not working on percentages??

[Rel3ntless]

Hi Guys,


I'm trying to apply conditional formatting on a script I've recorded for data exported from another program. So the conditional formatting I'm trying to apply is to color values between 70%-89% yellow and 90%-100% red. When applying these conditions the entire row is highlighted red. I then later discovered that it the conditions worked properly when the percentage signs were removed.


I then tried applying % format to these cells but got the error "style percentage not found". Apparently to fix this remedy involved merging the sheet with one that had the percentage style present but this isn't something I'd want to do everytime I export the data from the other program.


Is there a way around this? An alternative I was thinking was to program it to manually input the percentage signs back in after for cells between B2:I2 - can someone help me create the code for this and post? I'll also need the cell directly below to be coloured the same condition as above. ie: if 75%, color cell yellow along with the cell directly below.

So far I my script has been able to give me the following results:

https://drive.google.com/file/d/1Jsf-tWmCsXy0L-FbOrKy_5IslwdSiIIC/view?usp=sharing

What I'm trying to achieve:

https://drive.google.com/file/d/1bHUdG5ufqkqzp1ubHGfmWmSyiQjb0mP_/view?usp=sharing


here's my script code:

Sub TrafficMacro()
'
' TrafficMacro Macro
'


'
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Range("A4").Select
    Selection.Cut Destination:=Range("B4")
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Range("2:2,5:5,6:6,4:4,3:3").Select
    Range("A3").Activate
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Traffic Avails Week Starting"
    Range("A1:J3").Select
    With Selection
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("B2:I2").Select
    Cells.Replace What:="%", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Sellout%"
        Range("B2:I2").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=70", Formula2:="=89"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        End With
        Range("B2:I2").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=90"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    Range("H5").Select
    End With
    Range("A1:J3").Select
    Selection.Copy
End Sub

Any help would be greatly appreciated!

 

[Joe4]

Welcome to the Board!

I think your issue might be an error that people commonly make.
90% is really equal to .90 (not 90)!
So if you are using numbers without percent signs in your Conditional Formatting rules, they need to be those decimals.

 

[Rel3ntless]

Hi Joe4!

Thanks for your speedy response. I've tried using .90 (not 90) originally but the conditional formatting ended up highlighting the entire B2:I2. So that's why I opted to use "90" instead just so I could get it to highlight correctly. I also tried applying % to B2:I2 but got error msg "style percentage not found". This is what happens when I use ".90": https://drive.google.com/file/d/1k4pYZjfYl0wbrJ33PQ-nEEKq3flmYBl7/view?usp=sharing

Any help is greatly appreciated!

 

[Rel3ntless]

Here's the code too btw, added the decimals before the numbers:

Range("B2:I2").Select Cells.Replace What:="%", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Range("A2").Select
ActiveCell.FormulaR1C1 = "Sellout%"
Range("B2:I2").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=.70", Formula2:="=.89"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
Range("H5").Select
End With
Range("B2:I2").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=.90"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With

 

[Joe4]

If you are able to use "Replace" to get rid of the "%" signs, that tells me that your entries are Text, and not Numeric (in numeric percentages, the % isn't really part of the data, it is part of the format, which would not be affected by "Replace").

If that is the case (all your entries are Text), that would explain some of the issues that you are having, as it sounds like you are trying to impose numeric criteria on text, which won't work right (especially if trying to work with number ranges). You want your data to be numeric. You may be able to use Text to Columns on that column to convert the text entries to numeric ones.

 

[Rel3ntless]

I tried this but apparently it will only convert if it's the numbers are in the same column. Unfortunately the text I'm trying to convert are in a rwo. Is there a text to rows option?

Thanks again!

 

[Joe4]

Check out the options listed here: http://www.contextures.com/xlDataEntry03.html
There are VBA and non-VBA options.

 

[Rel3ntless]

Thanks for the link. As a test I managed to change the text numbers into percentage values and re-applied the conditional formatting but still got the same result illustrated in this link: https://drive.google.com/file/d/1k4p...ew?usp=sharing

 

[Joe4]

Unfortunately, I cannot see any of your links at my present location (corporate security blocks all those sites).
I may be able to look at it from my home computer later today or tomorrow.

 

[Rel3ntless]

Sorry for the double posting. Here's an embedded picture for you to see. Hope this helps and thanks again for your time and help!

[/URL][/IMG]