Conditional Formatting for more than 3 using referenced cells 1

[Neanie]

I have fours cells in a worksheet, each referencing a different cell within the same worksheet. I need to have each target cell change color (red, green, yellow, white or gray) depending on the value found in the referenced cell. When I use the traditional code (below), the color will not change in the target cell as the information changes in the referenced cell.

This is what I have so far, which will only work if the information is changed directly in the targeted cell:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then
Select Case Target
Case ""
icolor = 16
Case Is < 0.505
icolor = 3
Case 0.5 To 0.704
icolor = 6
Case Is > 0.904
icolor = 4
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub


Since the condition 0.705 to 0.904 is not defined, it will be white which is exactly what I want.

Also, is there there a way to target the four individual cells rather than having a range. The cells I wish to target are: K1, E2, H2 and K2.

Thanks in advance for any help!

Jeannine

 

[SkipVought]

Hi,

You never responded to the last suggestion I gave you in forum68. Did that not work?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Neanie]

My apologies, Skip.

I got side tracked yesterday and am finally able to get back to this project. I hadn't noticed you made another suggestion.

I will try it now.

Thanks!

Jeannine

 

[Neanie]

Skip,

Let me see if I understand your previous post correctly:

Select A1:B2

Format the interior for the default format.

Format CF

=H15=""

for your first criteria
and continue thru your remaing three.

I should use the tradition Microsoft CF to set each cell to choose a color (in my case, gray) if it's reference cell is blank. Then, add the VBA for the remaining three conditions?

Please view the title "TechnicalUser" very lightly. I am new to programming. :-\


Thanks for your help.

Jeannine

 

[Gruuuu]

You can actually still do this in Excel 2003 without VBA.

STEP 1. Set the fill color to Gray for whatever cell you want
STEP 2. Create CF in this fashion

   C1. Cell Value Is | greater than or equal to | .904 'Color
   C2. Cell Value Is | greater than or equal to | .704 'Color
   C3. Cell Value Is | greater than or equal to | .504 'Color

If you expect text in these cells
Change the comparison to BETWEEN, and for your highest (first) condition, just set the max well over your highest expected value.

 

[Gruuuu]

OOPS, sorry, forgot you wanted to target a different cell
so you do this:

   C1. Formula is | =H15>.904 | 'color
   ...etc

And if you need BETWEEN

   C1. Formula is | =AND(H15>.904,H15<9999) | 'Color
   ...etc

 

[SkipVought]

When you SELECT cells on the sheet and perform CF, each cell selected is formatted accordingly, with particular notice to the FORMULA REFERENCE; relative/relative, absolute/absolute, relative/absolute, absolute/relative.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Indeed. If your target cell is only on column H, you would have a Criteria like "=$H15>.904"
This is useful if you have other cells on the same row that use this format as well

 

[Neanie]

I understand what you each are saying. The only thing, I use white as a "color" too. Therefore, I technically have 5 conditions:

blank = gray
90% - 100% = green
70% - 90% = white
50% - 70% = yellow
>50% = red

I have found that using absolute referencing helps but once the color is set, it will not change when the reference cell does.

I received a recommendation from a co-worker to look into the Evaluate funcation. Any thoughts?

Thank you both!!

Jeannine

 

[SkipVought]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim icolor As Integer
    If Not Intersect(Target, [b]Union([K1], [E2], [H2], [K2])[/b]) Is Nothing Then
        Select Case Target
            Case ""
                icolor = 16
            Case Is < 0.505
                icolor = 3
            Case 0.5 To 0.704
                icolor = 6
            Case Is > 0.904
                icolor = 4
            Case Else
                'Whatever
        End Select
        
        Target.Interior.ColorIndex = icolor
    End If

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[glenntb]

Will this work for you?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim icolor As Integer
    
    For Each mycell In Array([K1], [E2], [H2], [K2])
        Select Case mycell.Value
            Case ""
                icolor = 16
            Case Is < 0.505
                icolor = 3
            Case 0.5 To 0.704
                icolor = 6
            Case Is > 0.904
                icolor = 4
            Case Else
                'Whatever
        End Select
        mycell.Interior.ColorIndex = icolor
        
    Next mycell

End Sub

Note: I'd recommend a default value for icolor in the "case else"

-Glenn

 

[Neanie]

I know it's been a while...

This project was a "do as you have time" project. I was finally able to get back to it and try out your suggestions.

Now it is my turn to thank you all for your assistance and let you know that Glenn's code worked like a charm.

I could not have done this without you all!

Thank you!!

Jeannine