Conditional formatting (interior cell color) with VBA

[Jmoz092]

I'm trying to modify the code found in this thread for a workbook I have. I've added the formulas that suit my conditional formatting requirements, but I need help/education with applying the rules to each row on the worksheet (2-31) and also modifying the sheet name(red lettering) reference in two of the rules (the sheet name is dynamic).

[COLOR=#454545][FONT=&quot][SIZE=2]Private Sub Worksheet_Change(ByVal Target As Range)[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]
[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]Dim thecell As Range[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]
[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]For Each thecell In Target[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]    If (Not IsEmpty(thecell)) Then[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]        With thecell[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            If .Value = " =and($I$5= ""Yes"", $K$5=""n/a"",MIN([COLOR=#ff0000]'Data 201707001'[/COLOR]!E49:E68)=0)" Then[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            ' red[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]                thecell.Interior.Color = RGB(252, 13, 27)

[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            ElseIf .Value = " =OR($I$5=""Yes"", $K$5=""No"")" Then[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            ' yellow[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]                .Interior.Color = RGB(255, 253, 56)

[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            ElseIf .Value = " =[COLOR=#ff0000]'Data 201707001'[/COLOR]!$I$34>45" Then[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            ' green[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]                 .Interior.Color = RGB(205, 254, 204)[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            Else

[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            ' white[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]                .Interior.Color = RGB(255, 255, 255)[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            End If[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]        End With[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]    Else[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]    'white[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]        thecell.Interior.Color = RGB(255, 255, 255)[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]    End If[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]Next[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]End Sub[/SIZE][/FONT][/COLOR]

 

[mumps]

Re: Conditional formatting (interior cell color) with VBA help

I've come up with this macro which would go into the worksheet code module for Sheet1 not a regular module. There are some aspects of it that I'm not comfortable with because I'm not sure if I have understood exactly what you want to do. Please have a look at it and hopefully you can follow what it is trying to do. It looks for changes in columns I or K and then depending on what the user enters in those columns the range from A to K is colored. Try the macro in a copy of your file. See if the code makes sense to you and if does what you want. Please let me know.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("I:I,K:K")) Is Nothing Then Exit Sub
    If Target.Column = 9 Then
        If Target = "Yes" And Target.Offset(0, 2) = "n/a" And WorksheetFunction.Min(Sheets(Cells(Target.Row, 4).Text).Range("E49:E68")) = 0 Then
            Range("A" & Target.Row & ":K" & Target.Row).Interior.ColorIndex = 3 'red
        End If
    ElseIf Target.Column = 11 Then
        If Target = "No" And Target.Offset(0, -2) = "Yes" Then
            Range("A" & Target.Row & ":K" & Target.Row).Interior.ColorIndex = 6 'yellow
        End If
    ElseIf Sheets(Cells(Target.Row, 4).Text).Range("I34") > 45 Then
        Range("A" & Target.Row & ":K" & Target.Row).Interior.ColorIndex = 4 'green
    Else
        Range("A" & Target.Row & ":K" & Target.Row).Interior.ColorIndex = xlNone
    End If
End Sub

 

[Jmoz092]

Re: Conditional formatting (interior cell color) with VBA help

I may not have conveyed info incorrectly. The worksheets that contain info for "I34" are hyperlinked and partially named. I'm sorry. The worksheets have "Data " preceding the text displayed in Column D. As such, the rows are not displayed with green interior color even though they meet the conditional formatting rules.

Also, it's error-ing at the first condition (red). All of the conditions are not entered at the same time by the user), and maybe that's causing the error?
Run time error 9
subscript out of range
It gave that when I changed I2 to Yes.

I tried changing it, but it still didn't recognize the intended worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("I:I,K:K")) Is Nothing Then Exit Sub
    If Target.Column = 9 Then
        If Target = "Yes" And Target.Offset(0, 2) = "n/a" And WorksheetFunction.Min(Sheets(Cells(Target.Row, 4).Text).Range("E49:E68")) = 0 Then
            Range("A" & Target.Row & ":K" & Target.Row).Interior.ColorIndex = 3 'red
        End If
    ElseIf Target.Column = 11 Then
        If Target = "No" And Target.Offset(0, -2) = "Yes" Then
            Range("A" & Target.Row & ":K" & Target.Row).Interior.ColorIndex = 6 'yellow
        End If
    ElseIf Sheets("Data" & Cells(Target.Row, 4).Text).Range("I34") > 45 Then
        Range("A" & Target.Row & ":K" & Target.Row).Interior.ColorIndex = 4 'green
    Else
        Range("A" & Target.Row & ":K" & Target.Row).Interior.ColorIndex = xlNone
    End If
End Sub

 

[mumps]

Re: Conditional formatting (interior cell color) with VBA help

In order to suggest a working macro, we have to know exactly what the data looks like and under which conditions the macro needs to be triggered. It looks like the situation is a little more complex than I thought. Perhaps you could upload a copy of your actual file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data. Include a detailed explanation of what you would like to do using a few specific examples from your data and referring to specific cells and worksheets. Also please explain what you mean by

All of the conditions are not entered at the same time by the user)

I can't promise a working solution but I'll give it another try.

 

[Jmoz092]

Re: Conditional formatting (interior cell color) with VBA help

... Also please explain what you mean by...

I was getting an error when I changed a value in column I, and the first conditional formatting formula in the VBA was all highlighted yellow. I was wondering if that was because all the conditional info was not available to the formula (column K and E49:E68 from the data worksheet).

 

[mumps]

Re: Conditional formatting (interior cell color) with VBA help

Will you be able to upload your file as described in Post #13 ?

 

[Jmoz092]

Re: Conditional formatting (interior cell color) with VBA help

No, unfortunately, I will not be able to do that.