Conditional Formatting based on another cells value VBA

[Musa15]

Hi,

Hope I can find some insight here. I am working on highlighting cells in column Y and V $Y$2:$Y$35 / $V$2:$V$35 based on formula rule on $$AA2 values. Can anyone offer guidance on how to do this on VBA? Basically, if cells in AA2 do not = 0, are greater than or less than zero then I would need the respective V and Y column to become red. When I record myself doing the conditional formatting I am unable to get the code to yield highlighted cells. Additionally, my current way of highlighting it manually highlights cells that are blank :/ any insight would be much appreciated.

[/URL][/IMG]

 

[Mitrix82]

Hi,
Are you asking to get the same results as the conditional formatting but using vba code? I have copied your rules and have not found any issues with highlighting unless auto-calculation is disabled. Are you using manual calculation?

 

[Musa15]

Yes, I am trying to get the same results using VBA code. For some reason, when recording myself and playing it back I am not getting any highlighting.

I have this going on:

Sub Macro10()
'
' Macro10 Macro
'

'
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$AA2<0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ColorIndex = xlAutomatic
End With
Selection.FormatConditions(1).StopIfTrue = False
Cells.FormatConditions.Delete
Range("AA1").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ColorIndex = xlAutomatic
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("Y2:Y35").Select
Range("AA1").Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$AA2<0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ColorIndex = xlAutomatic
End With
Selection.FormatConditions(1).StopIfTrue = False

End Sub

 

[Mitrix82]

Hi,
The top cell in your selection will always refer to AA2 not the same row as the selection.

 

[Musa15]

Hi,
The top cell in your selection will always refer to AA2 not the same row as the selection.

Hi,

I don't understand. What should it look like, so that it references the correct row based on value of not zero? thank you for your help btw, I'm teaching myself vba and appreciate the help on this forum.