Conditional formatting not working on percentages??

R

Rel3ntless

New Member
Joined
Nov 28, 2017
Messages
14
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:

view
view
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:

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!
 
Sort by date Sort by votes
I am not sure if that posting attempt did not worked, or it is being blocked for me, but I am not seeing anything.
Do you currently see it in the post?
 
Last edited:
Upvote 0

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
OK. That means that I will be able to see it from my home computer, just not here at my current location.
 
Upvote 0
OK, I can see your images, but without seeing all your formulas and such, it is very hard to figure out .
If you could upload your file of data (with formulas), and the latest version of your VBA code, I might be able to figure out what is going on.
 
Upvote 0
I see now. Your percents are actually entered as Text, so would need to be converted to values in your Conditional Formatting formulas.
Also, since you want to apply this to multiple rows based on the value in a single row, I think you need to use the Formula option.

Here is the Conditional Formatting part of the code that will do what you want:
Code: 
    Range("B2:I3").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(VALUE(B$2)>=70%,VALUE(B$2)<=89%)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(VALUE(B$2)>=90%,VALUE(B$2)<=100%)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
I tested it out and confirmed that it does return the exact results you want to see.
 
Last edited:
Upvote 0
Hi Joe4,

This works perfectly - thank you so much!

I've got one more report where it spits out the data a little differently and when I tried applying your code to my macro I wasn't able to get the desired result in having the percentages and color conditions.

If you have time to take a look I've attached the files I used here: https://drive.google.com/file/d/18jCBP4C8ncqs-cGqN0pMKlp-KEbPTKD_/view?usp=sharing

Thanks again so much I really appreciate your help, lmk if you need anything else!!
 
Upvote 0
Rel3ntless said:
Hi Joe4,

This works perfectly - thank you so much!

I've got one more report where it spits out the data a little differently and when I tried applying your code to my macro I wasn't able to get the desired result in having the percentages and color conditions.

If you have time to take a look I've attached the files I used here: https://drive.google.com/file/d/18jCBP4C8ncqs-cGqN0pMKlp-KEbPTKD_/view?usp=sharing

Thanks again so much I really appreciate your help, lmk if you need anything else!!
Click to expand...

Here's the code I used - as you can see I merged your code with mine but for some reason the percentages and color conditions didn't work.
Code: 
Sub TrafficClassicalMacro()'
' TrafficClassicalMacro Macro
'


'
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Classical Traffic Avails Week Starting"
    Range("B2").Select
    Columns("B:B").EntireColumn.AutoFit
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "11/27/17 Traffic Spon M-F 5a-7p"
    Range("C1:J1").Select
    Selection.Replace What:=" Traffic Spon M-F 5a-7p", Replacement:="", LookAt _
        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("C:C").EntireColumn.AutoFit
    Columns("J:J").EntireColumn.AutoFit
    Columns("F:F").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:E").EntireColumn.AutoFit
    Columns("G:G").EntireColumn.AutoFit
    Rows("2:27").Select
    Selection.Delete Shift:=xlUp
    Range( _
        "3:9,11:11,12:12,13:13,14:14,15:15,16:16,17:17,18:18,19:19,20:20,21:21,22:22,23:23,24:24" _
        ).Select
    Range("A24").Activate
    Selection.Delete Shift:=xlUp
    Range("A2:A3").Select
    Selection.Cut Destination:=Range("B2:B3")
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1:J3").Select
    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
    With Selection
        .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
        Columns("A:J").EntireColumn.AutoFit
    End With
   Range("B2:I3").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(VALUE(B$2)>=70%,VALUE(B$2)<=89%)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(VALUE(B$2)>=90%,VALUE(B$2)<=100%)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
        Range("A1:J3").Select
    Selection.Copy
End Sub

Thanks again in advance!
 
Last edited:
Upvote 0
Sorry, I did not get a chance to look at this yesterday when I was at home where I can see your original files.
I'll see if I can find some time this evening when I am home.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
formating question
Replies
3
Views
324
Micron
M
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
600
RoryA
RoryA
K
  • Solved
create a button that do actions
2 3
Replies
22
Views
1K
kerm007
K
P
  • Question Question
Issue with button not running in spread sheet only in test mode
Replies
1
Views
379
6StringJazzer
6StringJazzer
J
  • Question Question
Issue with Macros and renaming files
Replies
6
Views
1K
kevin9999
K

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top