none
Conditional Formatting - Run on multiple sheets RRS feed

  • General discussion

  • Hi there,

    I need to apply the conditional formatting on one column (Column C) based on values of Column E. I recorded macro and trying convert it to a code run on multiple sheets but I am unable to generalize it wrt sheets.

    I want to add a between condition on Column C to highlight the cells between E2 & E3 in each sheet. 

    my sample file with macro can be accessible via the below link

    https://drive.google.com/file/d/1VxsJqQnEieClFgwpzDxqIakBGoe-xUva/view?usp=sharing

    Regards, Sara

    Sub SortingSheets()
        Dim sheetNo As Long
      Dim Wkst As Excel.Worksheet
        For sheetNo = 1 To ThisWorkbook.Worksheets.Count
        Set Wkst = ThisWorkbook.Worksheets(sheetNo)
        Call Worksheet_Change(Wkst)
        Call AddHeadings(Wkst)
        Call Enter_Formula(Wkst)
        Call Enter_Formula1(Wkst)
        Call ConditionalForm(Wkst)
        Next
    End Sub
    
    Sub ConditionalForm()
        WK.Columns("C:C").Select
        Application.CutCopyMode = False
        Application.CutCopyMode = False
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
            Formula1:="=$E$2", Formula2:="=$E$3"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .Color = -16383844
            .TintAndShade = 0
        End With
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13551615
            .TintAndShade = 0
        End With
    Selection.FormatConditions(1).StopIfTrue = False
    End Sub
    

    Friday, August 14, 2020 4:02 AM

All replies

  • Change ConditionalForm as follows:

    Sub ConditionalForm(WK As Worksheet)
        Dim FC As FormatCondition
        Set FC = WK.Range("C:C").FormatConditions.Add(Type:=xlCellValue, _
            Operator:=xlBetween, Formula1:="=$E$2", Formula2:="=$E$3")
        With FC
            .SetFirstPriority
            With .Font
                .Color = -16383844
                .TintAndShade = 0
            End With
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = 13551615
                .TintAndShade = 0
            End With
            .StopIfTrue = False
        End With
    End Sub


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Friday, August 14, 2020 6:21 AM
  • Hello Hans,

    Thanks heaps for the codes,it worked.

    Regards, Sara


    Friday, August 14, 2020 10:59 PM