conditional formatting based on other cell (over multiple Rows & Column)

This code will work if you remove the hidden columns and place those calculations some place else on your worksheet.

Option Explicit

Sub FilRed()
    Dim c As Range
    Dim rng As Range
    Set rng = Range("D14:AH18")
    
    For Each c In rng
    If c = "SL" Then
    c.Offset(0, 39).Interior.ColorIndex = 3
    c.Offset(0, 40).Interior.ColorIndex = 3
    End If
    Next c
    

End Sub

I don't see another option with my skill level. Hopefully, someone else with more advanced VBA will have a solution for you.

Use of Worksheet_change event could be an option.



For example.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim LstRW As Long
    Dim rng As Range, r As Integer

    If Target.Count > 1 Then Exit Sub

    LstRW = Cells(Rows.Count, "D").End(xlUp).Row
    Set rng = Range("D14:AH" & LstRW)

    If Not Application.Intersect(Target, rng) Is Nothing Then    ' indicates the Target range
        r = Target.Row
        If Target.Value = "SL" Then
            Range(Cells(r, "AQ"), Cells(r, "FH")).Interior.Color = vbRed
        Else
            Range(Cells(r, "AQ"), Cells(r, "FH")).Interior.Color = xlNone
        End If
    End If

End Sub

See attached example, change the cells in the dropdown.

This is a macro enabled workbook.

I'm not sure if you mean Highlight or select, here you go with select, you should be able to figure out highlight if that's what you want.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cRng As Range    ',LstRW As Long
    Dim rng As Range, r As Integer, c As Integer, Frng As Range, cl As Range

    If Target.Count > 1 Then Exit Sub

    'LstRW = Cells(Rows.Count, "D").End(xlUp).Row
    Set rng = Range("D14:AH18")


    Set Frng = Range("AP12:FH12")
    If Not Application.Intersect(Target, rng) Is Nothing Then    ' indicates the Target range
        If Target.Value = "SL" Then
            Set cl = Frng.Find(what:=Cells(12, Target.Column).Value, lookat:=xlWhole)
            If Not cl Is Nothing Then
                r = Target.Row
                c = cl.Column
                Set cRng = Range(Cells(r, c), Cells(r, c + 1))
                cRng.Select
            Else: MsgBox "Not Found"
                Exit Sub
            End If
        End If
    End If

End Sub

Conditional formatting is an option if you are allowed to modify the range: AQ11:FH12 by Unmerging the cells, putting the day and day number in each column. Then, in Aq14: FH18, use:

=INDEX($D$14:$AH$18,ROW()-13,MATCH(AQ$12,$D$12:$AH$12,0))="SL"

notice that starting in column AR11:AR12, alternamte columns are formatted with white font.

Originally Posted by protonLeah

nevermind....

Hmmm..why not?

Dave, my original solution (=COUNTIF($D14:$AH14,"SL")>0), formatted the entire row in range AQ14:FH18 if there was any SL in the corresponding row of columns D:AH. The new one only formats cells corresponding to the day with the SL entry. But it requires modifying the cells in AQ11:FH12 which may not be allowed(? ).