More than 3 cell highlite colors ?

[Guest]

I have values created by a formula from info pasted into other cells
in X17 to X24, Y17 to Y24. Again in AC17 to AC24, AD17 to AD24. These IF
formulas use ="","", to present a blank cell if no data is available.
In G29 to G36 I use =X17 to X24 and I29 is =Y17 etc,to move this result
to a report cover page. I use CF to change the cell background color
in G29 to G36 and I29 to I36 depending on what values appear there, as
G29 = 95%, green I29 = A, green, etc. It works great, but....
Now I need a fourth color. Add in's are forbidden.
I have VB that will change cell color in G29 to G36, etc, but only
when values are typed directly in these cells. It will not update
G29 when the value in X17 changes. Is there a way to modify it
so that it will copy the value in X17 to G29, etc, and highlite the
cell to a specified color per value ?
Using Office 2000.
Thanks

 

[Guest]

Hopefully, this will help:

http://www.mcgimpsey.com/excel/conditional6.html

 

[Guest]

Thanks, I checked the page you refer to. It deals with changing font
color. I need to change cell color ? Wouldn't that be a different method ?

 

[Guest]

Sorry, my mistake.

I assume your reference to VB means you are using an event handler - perhaps
the worksheet Change or Worksheet Calculate event? I would think either one
of these would work to test the values in column G and I and change the cell
color.

My thought would be something like this. Right click on you sheet tab,
select view code and paste it into your code module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("G29").Value = 0.95 Then _
Range("G29").Interior.ColorIndex = 10
If Range("I29").Value = "A" Then _
Range("I29").Interior.ColorIndex = 10
End Sub

The event handler will fire if cell G29 or X17 changes (or any cell on the
worksheet - for that matter). If that doesn't help, please post your code.

 

[Guest]

Forgot to remove the color if the condition is not met.

Private Sub Worksheet_Change(ByVal Target As Range)
With Range("G29")
If .Value = 0.95 Then
.Interior.ColorIndex = 10
Else: .Interior.ColorIndex = xlNone
End If
End With

With Range("I29")
If UCase(.Value) = "A" Then
.Interior.ColorIndex = 10
Else: .Interior.ColorIndex = xlNone
End If
End With

End Sub

 

[Guest]

Thanks, this looks good. It will be a few hours before I'll get
a chance to try it. I'll post the results.

 

[Bob Phillips]

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks for the help, but I don't know enough about the code to be able to
modify it to cover the all the variables. It doesn't update the color when new
data is pasted into the worksheet (=x17) etc. The code necessary to cover
this is probably to complex to be pratical anyway.
Thanks again.