Conditional Formatting using VBA for comparing range to set limits

[dino4u86]

Hi,

Im looking to create a VBA code that help me check my results for each region (Range A2:P9) against the limits of that region Range(G2:G9)

Results in Region 1 if greater than Limit of region 1 then highlight cell will color red. same goes for other regions

Request VBA code for the same.

Thanks

 

[Fluff]

Select B2:F9 & use this formula in conditional formatting.

=AND($B2<$G2,$B2<>"")

 

[dino4u86]

That doesnt work as the entire row in the table gets selected and colored which is not what I want. I would like only the cell in every individual region to be colored that are breaking the limits and I need this in VBA please

attaching incorrect result

 

[Fluff]

OOps, you need to remove the $ sign from both B2 references, but leave it on the G2

 

[dino4u86]

Thanks I managed to write the code:

'conditional formatting
ThisWorkbook.Sheets("XYZ").Activate
'Dim rng1 As Range
rng1 = Range(Range("F3"), Range("P3").End(xlDown).Offset(-2, 0)).Select
Range("rng1").FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=Abs(F3)>$P3"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = False

 

[Fluff]

Glad to help & thanks for the feedback.