PDA

View Full Version : [SOLVED] Conditional Formatting Help



rajkumar
03-31-2018, 04:25 PM
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
03-31-2018, 10:11 PM
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
03-31-2018, 10:49 PM
=And(B2>A2,A1="broken")
=And(B2>A2,A1<>"broken")

mana
03-31-2018, 10:57 PM
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
03-31-2018, 11:21 PM
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
03-31-2018, 11:42 PM
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
04-01-2018, 12:43 AM
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