Conditional Formatting Help

[rajkumar]

Hi

i want to highlight cell B2 with red if cell B2>A2 and if A1 value is "broken" , and also B2 with green if cell B2>A2 and if A1 is not "broken".

How to do conditional formatting for this ?

please help

raj

[rajkumar]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim parameter As String


parameter = Sheets("REGION").Range("$C$35").Value


Select Case parameter


Case "RT_MET", "RES_MET", "RES_MET_WP", "RES_MET_WOP"
Cells.FormatConditions.Delete
Range("$E$37:$P$102").Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=$D$35"
Range("$E$37:$P$102").FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Range("$E$37:$P$102").FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -11489280
.TintAndShade = 0
End With

Range("$E$37:$P$102").Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, _
Formula1:="=$D$35"
Range("$E$37:$P$102").FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Range("$E$37:$P$102").FormatConditions(2).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With

Case "BROKEN_CALLS", "BROKEN_CALLS_WP", "BROKEN_CALLS_WOP"


Cells.FormatConditions.Delete
Range("$E$37:$P$102").FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="=$E$35"
Range("$E$37:$P$102").FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Range("$E$37:$P$102").FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -11489280
.TintAndShade = 0
End With
Range("$E$37:$P$102").Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=$E$35"
Range("$E$37:$P$102").FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Range("$E$37:$P$102").FormatConditions(2).Font
.Bold = True
.Italic = False
.Color = -16776961
.TintAndShade = 0
End With
End Select


End Sub
iam trying to achieve this using worksheet selection change event, but it is throwing error "subscript out of range"
could not figure out what is the issue, please help

[mana]

=And(B2>A2,A1="broken")
=And(B2>A2,A1<>"broken")

[mana]

Option Explicit

Sub test()
Dim fc As FormatConditions

Set fc = Range("B2").FormatConditions
fc.Delete
fc.Add(Type:=xlExpression, Formula1:="=And(B2>A2,A1=""broken"")").Interior.Color = vbRed
fc.Add(Type:=xlExpression, Formula1:="=And(B2>A2,A1<>""broken"")").Interior.Color = vbGreen

End Sub

[rajkumar]

Option Explicit

Sub test()
Dim fc As FormatConditions

Set fc = Range("B2").FormatConditions
fc.Delete
fc.Add(Type:=xlExpression, Formula1:="=And(B2>A2,A1=""broken"")").Interior.Color = vbRed
fc.Add(Type:=xlExpression, Formula1:="=And(B2>A2,A1<>""broken"")").Interior.Color = vbGreen

End Sub


works like a charm!

what if i apply to multiple ranges

$E$37:$P$102, $R$37:$U$102, $W$37:$X$102, $Z$37:$Z$102 > A2 and A1=""broken"

please reiterate code

[mana]

Why do you use macro?
=And(E37>$A$2,$A$1="broken")
=And(E37>$A$2,$A$1<>"broken")


Option Explicit

Sub test()
Dim r As Range
Dim fc As FormatConditions

Set r = Range("E37:P102, R37:U102, W37:X102, Z37:Z102")

Set fc = r.FormatConditions
fc.Delete
fc.Add(Type:=xlExpression, Formula1:="=And(E37>$A$2,$A$1=""broken"")").Interior.Color = vbRed
fc.Add(Type:=xlExpression, Formula1:="=And(E37>$A$2,$A$1<>""broken"")").Interior.Color = vbGreen

End Sub

[rajkumar]

Why do you use macro?
=And(E37>$A$2,$A$1="broken")
=And(E37>$A$2,$A$1<>"broken")


Option Explicit

Sub test()
Dim r As Range
Dim fc As FormatConditions

Set r = Range("E37:P102, R37:U102, W37:X102, Z37:Z102")

Set fc = r.FormatConditions
fc.Delete
fc.Add(Type:=xlExpression, Formula1:="=And(E37>$A$2,$A$1=""broken"")").Interior.Color = vbRed
fc.Add(Type:=xlExpression, Formula1:="=And(E37>$A$2,$A$1<>""broken"")").Interior.Color = vbGreen

End Sub


thanks work fantasitic

why i am using a macro because , user selects a drop down that has values other than "broken", so wanted to run a macro when worksheet selection change event