Conditional Formatting- Refresh

[Ram B]

I have added a VB script to a sheet to change color of the cell based on input
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("F1:F510")) Is Nothing Then
Select Case Target
Case "Red"
icolor = 3
Case "Green"
icolor = 4
Case "Blue"
icolor = 5
Case "White"
icolor = 2
Case "Gray"
icolor = 15
Case ""
icolor = 0
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
Target.Font.ColorIndex = icolor

End If

End Sub

 

[Bob Phillips]

Use the calculate event and cycle through each cell in the range.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Alan]

The reason it does not change the cell with the formula is because it is not
the "target" cell, but is only affected by the "target" cell.

I don't know what version you are running. I am running 2002. In 2002
Conditional Formatting has all the Excel standard colors, not just 3.

Format
Conditional Formatting
Click the Format button
Click the Pattern tab.
All Excel standard colors are available here.

Conditional Formatting is the easiest way to go. You could calculate each
cell in the range after a change occurs.


Alan

 

[Ram B]

I am engineer with little knowledge of VB. Would it be possible for you to
help me with the code?

 

[Bob Phillips]

Here you are

Private Sub Worksheet_Calculate()
Dim icolor1 As Long
Dim icolor2 As Long
Dim cell As Range

For Each cell In Range("F1:F510")

icolor1 = xlColorIndexNone
icolor2 = xlColorIndexAutomatic
Select Case cell.Value
Case "Red": icolor1 = 3: icolor2 = 3
Case "Green": icolor1 = 4: icolor2 = 4
Case "Blue": icolor1 = 5: icolor2 = 5
Case "White": icolor1 = 2: icolor2 = 2
Case "Gray": icolor1 = 15: icolor2 = 15
Case Else: 'Whatever
End Select

cell.Interior.ColorIndex = icolor1
cell.Font.ColorIndex = icolor2
Next cell

End Sub

 

[Bob Phillips]

Not 3 colours, 3 conditions.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ram B]

Thanks Works like a charm

Here you are

Private Sub Worksheet_Calculate()
Dim icolor1 As Long
Dim icolor2 As Long
Dim cell As Range

For Each cell In Range("F1:F510")

icolor1 = xlColorIndexNone
icolor2 = xlColorIndexAutomatic
Select Case cell.Value
Case "Red": icolor1 = 3: icolor2 = 3
Case "Green": icolor1 = 4: icolor2 = 4
Case "Blue": icolor1 = 5: icolor2 = 5
Case "White": icolor1 = 2: icolor2 = 2
Case "Gray": icolor1 = 15: icolor2 = 15
Case Else: 'Whatever
End Select

cell.Interior.ColorIndex = icolor1
cell.Font.ColorIndex = icolor2
Next cell

End Sub


--
__________________________________
HTH

Bob

 

[Alan]

Thanks Bob, I didn't catch that. I'll read closer next time.

Alan