PDA

View Full Version : [SOLVED] Can someone tell me why this doesn't work.



LutonBarry
01-31-2017, 09:58 AM
Hello folks,

Please refer to the attached macro enabled spreadsheet. I want to format the rows based on the value in Column L on the following basis:

If L is <35% colour the row Green.
If L less <75% >=35% then Amber
If L >=75% then Red.

The code I've written works on the rows that only contain data but colours all rows Red, Green or Amber regardless of the value in Column L.



Where have I gone wrong?

Thanks.

Paul_Hossler
01-31-2017, 10:41 AM
Try this



Option Explicit

Sub CondFormat()

With Sheets("Jeopardy").Columns("A:L")
.FormatConditions.Delete

.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($B1=""ServiceRequest4"",$L1<.75)"
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($B1=""ServiceRequest4"",$L1<.35)"
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($B1=""ServiceRequest4"",$L1>=.75)"

'Colours SR-4 and <75% rows Amber
With .FormatConditions(1)
.SetFirstPriority
With .Interior
.PatternColorIndex = xlAutomatic
.Color = 49407 'Amber
.TintAndShade = 0
End With
.StopIfTrue = False
End With

'Colours SR-4 and <35% rows Green
With .FormatConditions(2)
.SetFirstPriority
With .Interior
.PatternColorIndex = xlAutomatic
.Color = 5287936 'Green
.TintAndShade = 0
End With
.StopIfTrue = False
End With

'Colours SR-4 and >=75% rows Red
With .FormatConditions(3)
.SetFirstPriority
With .Interior
.PatternColorIndex = xlAutomatic
.Color = 255 'Red
.TintAndShade = 0
End With
.StopIfTrue = False
End With
End With
End Sub

LutonBarry
01-31-2017, 05:29 PM
Paul,

Many thanks for that. It's done the trick.

If I wanted to remove the reference to column B

Would I modify the formula from

.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($B1=""ServiceRequest4"",$L1<.75)"

To
.FormatConditions.Add Type:=xlExpression, Formula1:="=($L1<.75)"

Paul_Hossler
01-31-2017, 06:05 PM
Not sure I understand, but try it and see if it's what you want