Cell color updates - VBA instead of Conditional Formatting

[Tom]

I would like to add some code into the UDF below to
change background and font colors depending with value is
select.

I do NOT want to simply use CONDITIONAL FORMATTING as I
will have more than just these 3 cases in the example
below.

Does anyone have some pointers as to what lines of code
need to be added for changing background color to
e.g. "red" and font color to "white" if case 1 is met?

Thanks in advance,
Tom


&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Function Number(Test As String)
Select Case Test
Case Is = 1
Stoplight = "Red"
Case Is = 2
Number = "Yellow"
Case Else
Number = "Green"
End Select
End Function
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

 

[Vasant Nanavati]

Hi Tom:

If you are going to call this function from a worksheet, it will *not* be
able to change cell colors. Worksheet functions cannot make changes to
cells.

Regards,

Vasant.

 

[Gord Dibben]

Tom

You can use event code behind the worksheet.

Right-click on the worksheet tab and select "view code". Paste the code in
there. Adapt to suit your needs. Note it works on Column A.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim Rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
For Each Rng In vRngInput
'Determine the color
Select Case Rng.Value
Case Is <= 0: Num = 10 'green
Case 0 To 5: Num = 1 'black
Case 5 To 10: Num = 5 'blue
Case 10 To 15: Num = 7 'magenta
Case 15 To 20: Num = 46 'orange
Case Is > 20: Num = 3 'red
End Select
'Apply the color
Rng.Interior.ColorIndex = Num
Next Rng
End Sub

Gord Dibben Excel MVP XL2002

 

[Tom]

Gord:

Thanks for the feedback... I thought that this must be
possible via a function. This works great!

I have 2 follow-up questions though...

1. How can I ensure that all cell background colors be
updated dynamically?

For insance, I entered all numbers that would highlight
the appropriate background color. I then changed the
NUM value (1 to 6; black to yellow) in the function.
Althought, yellow be the the color when entering i.e. 4
now, it did not update all previous values where the
background color was black.


2. I tried to integrate the change of fontcolor as
well. I wasn't entire certain as to how I need to
update the function. I truly would appreciate if you
could review the "changes" below and provide me some
pointers as to how I should rewrite it that i.e. the
background color changes to "black" and the fontcolor
changes to "white" in its criteria are met.


Thanks so much in advance,
Tom


&&&&&&&&&&&&&&&&&&&&&&&&&&&

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim FontNum As Long
Dim Rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
For Each Rng In vRngInput

'Determine the color
Select Case Rng.Value
Case Is <= 0: Num = 10 And FontNum =
5 'green
Case 0 To 5: Num = 6 'yellow
Case 5 To 10: Num = 5 'blue
Case 10 To 15: Num = 7 'magenta
Case 15 To 20: Num = 46 'orange
Case Is > 20: Num = 3 'red
End Select

'Apply the color
Rng.Interior.ColorIndex = Num
Rng.Font.ColorIndex = FontNum
Next Rng
End Sub

-----Original Message-----
Tom

You can use event code behind the worksheet.

Right-click on the worksheet tab and select "view code". Paste the code in
there. Adapt to suit your needs. Note it works on Column A.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim Rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
For Each Rng In vRngInput
'Determine the color
Select Case Rng.Value
Case Is <= 0: Num = 10 'green
Case 0 To 5: Num = 1 'black
Case 5 To 10: Num = 5 'blue
Case 10 To 15: Num = 7 'magenta
Case 15 To 20: Num = 46 'orange
Case Is > 20: Num = 3 'red
End Select
'Apply the color
Rng.Interior.ColorIndex = Num
Next Rng
End Sub

Gord Dibben Excel MVP XL2002

 

[Gord Dibben]

Tom

1. A worksheet_change event will not update dynamically. You must re-enter
or copy/paste in place.

2. Modify as below for Font Colors.....

Case 0 To 5: Num = 6: FontNum = 1 'yellow and black
Case 5 To 10: Num = 5: FontNum = 2 'blue and white

Case 10 To 15: Num = 7 'magenta
Case 15 To 20: Num = 46 'orange
Case Is > 20: Num = 3 'red
End Select

'Apply the color
Rng.Interior.ColorIndex = Num
Rng.Font.ColorIndex = FontNum
Next Rng
End Sub

Gord

Gord:

Thanks for the feedback... I thought that this must be
possible via a function. This works great!

I have 2 follow-up questions though...

1. How can I ensure that all cell background colors be
updated dynamically?

For insance, I entered all numbers that would highlight
the appropriate background color. I then changed the
NUM value (1 to 6; black to yellow) in the function.
Althought, yellow be the the color when entering i.e. 4
now, it did not update all previous values where the
background color was black.


2. I tried to integrate the change of fontcolor as
well. I wasn't entire certain as to how I need to
update the function. I truly would appreciate if you
could review the "changes" below and provide me some
pointers as to how I should rewrite it that i.e. the
background color changes to "black" and the fontcolor
changes to "white" in its criteria are met.


Thanks so much in advance,
Tom


&&&&&&&&&&&&&&&&&&&&&&&&&&&

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim FontNum As Long
Dim Rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
For Each Rng In vRngInput

'Determine the color
Select Case Rng.Value
Case Is <= 0: Num = 10 And FontNum =
5 'green
Case 0 To 5: Num = 6 'yellow
Case 5 To 10: Num = 5 'blue
Case 10 To 15: Num = 7 'magenta
Case 15 To 20: Num = 46 'orange
Case Is > 20: Num = 3 'red
End Select

'Apply the color
Rng.Interior.ColorIndex = Num
Rng.Font.ColorIndex = FontNum
Next Rng
End Sub