Changing the color of a cell in another column 1

[nyteshades]

I'm hoping someone can point me in the right direction with this one.

The idea is, the cells in column D (starting at D7 and going to d204) will change the color of the corresponding cell in column G. So with "GO" in D7, the color in G7 would be green...or in D100, "MBR" is selected causing G100 to trigger red.

I have the color thing sorted out, but I am struggling with the range and making the G cell do what I want. Specifically this segment

    If Target.Address < "$D$7" Or Target.Address < "$D$240" Then Exit Sub
         Set r = Range("G7")

Here's the whole code segment

Private Sub Worksheet_Change(ByVal Target As Range)

'Declare variables
Dim r As Range
Dim red1, red2, red3 As String                                                      ' Red variables are used for conditions that trigger red events in column G
Dim yellow1, yellow2, yellow3, yellow4, yellow5 As String                           ' Yellow variables are used for conditions that trigger yellow events in column G

' set red and yellow variables with strings

red1 = "MRB"
red2 = "30 day SRP Missing Medical Doc"
red3 = "30 day SRP Scheduled MRB"
yellow1 = "Pending Lab"
yellow2 = "Labs Abnormal"
yellow3 = "Medical Doc's needed from SM"
yellow4 = "PCP with Follow on Medical Docs"
yellow5 = "Lab and Medical Doc Follow-up"


    If Target.Address < "$D$7" Or Target.Address < "$D$240" Then Exit Sub
         Set r = Range("G7")

        If Target = red1 Or Target = red2 Or Target = red3 Then
            With r
            .Value = "Red"
            .Interior.ColorIndex = 3
        End With

            ElseIf Target = yellow1 Or Target = yellow2 Or Target = yellow3 Or Target = yellow4 Or Target = yellow5 Then
                With r
                .Value = "Yellow"
               .Interior.ColorIndex = 6
        End With

            ElseIf Target = "Go" Then
                With r
                .Value = "Green"
                .Interior.ColorIndex = 4
           End With
    End If

End Sub

Thanks for any help

 

[PHV]

Did you even try conditional formartting ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nyteshades]

No, boss said VB...so that's what I'm doing.

 

[SkipVought]

As PHV has suggested, this can simply and easily be done using Conditional Foramtting.

If your boss suggested otherwise, he or she does not understand the basic features of native Excel.

1. Set up a lookup table on a new sheet, just to keep it out of the way, sorted...
[tt]
30 day SRP Missing Medical Doc red
30 day SRP Scheduled MRB red
Lab and Medical Doc Follow-up yellow
Labs Abnormal yellow
Medical Doc's needed from SM yellow
MRB red
PCP with Follow on Medical Docs yellow
Pending Lab yellow
[/tt]
Name the entire range ColorTable

Select the column G range, top down.

Set the Cell > Format - Patterns TAB -- Color shading to GREEN.

Open the CF wizard Format > Conditional Formattting...

Use this Formula is: formulas for TWO conditions...
[tt]
Cond1: =VLOOKUP(D1,ColorTable,2)="red"
Set the Format - Patterns TAB -- Color shading to RED

Cond2: =VLOOKUP(D1,ColorTable,2)="yellow"
Set the Format - Patterns TAB -- Color shading to YELLOW
[/tt]
Should take less than 20 minutes.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mintjulep]

Skip,

I don't know that I would have ever thought about using a table and a vlookup as the formula for conditional formatting. Pretty neat.

Consider expanding your reply into a FAQ in the MS Office forum.