my current code is:
Sheets("Time_cycle_of_S5_requests_UK").Select
Columns("H:J").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Columns("H:J").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=7"
Selection.FormatConditions(Selection.FormatConditions.Count).SetLastPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
however this doesnt seem to do what i want it to do...
i wish for all values less then 0 to be in red and all values more then 7 to be in orange.
as they dont conflict i shouldnt have to set priority.
however what actually happens is i get the "less then 0" condition to be orange and the more than 7 condition is just normal formatting... how can i fix this?
EDIT: i was missing the line
Selection.FormatConditions(1).StopIfTrue = False
however this now makes my header orange... is there any way to prevent that without setting a range like "H2:J30000"
as my data size could increase i would like to be able to add to the data without having to re-highlight or increase the range of the formatting.
Bookmarks