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
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
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).Se tFirstPriority
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).Se tFirstPriority
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).Se tFirstPriority
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).Se tFirstPriority
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
=And(B2>A2,A1="broken")
=And(B2>A2,A1<>"broken")
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
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