Consulting

Results 1 to 7 of 7

Thread: Conditional Formatting Help

  1. #1

    Conditional Formatting Help

    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

  2. #2
    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

  3. #3
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    =And(B2>A2,A1="broken")
    =And(B2>A2,A1<>"broken")

  4. #4
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    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

  5. #5
    Quote Originally Posted by mana View Post
    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

  6. #6
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    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

  7. #7
    Quote Originally Posted by mana View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •