More than 3 conditional formats in excel
 

I have used the below code to get around the 3 conditional formats in excel.
But this only work when new values are entered. How do get this code to work
for values which I have already entered.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
If Not Intersect(Target, Range("C4:IR30")) Is Nothing Then
Select Case Target
Case "C", "c"
icolor = 8 'light blue
Case "D", "D"
icolor = 9 'brown
Case "G", "g"
icolor = 6 'yellow
Case "H", "h"
icolor = 3 'red
Case "K", "k"
icolor = 7 'pink
Case "L", "l"
icolor = 4 'green
Case "O", "o"
icolor = 20 'light blue
Case "S", "s"
icolor = 10 'dark green
Case "C", "c"
icolor = 8 'light blue
Case "X", "x"
icolor = 15 'grey
Case Else
'Whatever '1 black, 2 white, 5 dark blue, 11 dark blue
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub

More than 3 conditional formats in excel
 

This should help

http://www.microsoft.com/office/comm...7-ee3abfdb66d9

"Manj" wrote:

I have used the below code to get around the 3 conditional formats in excel.
But this only work when new values are entered. How do get this code to work
for values which I have already entered.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
If Not Intersect(Target, Range("C4:IR30")) Is Nothing Then
Select Case Target
Case "C", "c"
icolor = 8 'light blue
Case "D", "D"
icolor = 9 'brown
Case "G", "g"
icolor = 6 'yellow
Case "H", "h"
icolor = 3 'red
Case "K", "k"
icolor = 7 'pink
Case "L", "l"
icolor = 4 'green
Case "O", "o"
icolor = 20 'light blue
Case "S", "s"
icolor = 10 'dark green
Case "C", "c"
icolor = 8 'light blue
Case "X", "x"
icolor = 15 'grey
Case Else
'Whatever '1 black, 2 white, 5 dark blue, 11 dark blue
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub

More than 3 conditional formats in excel
 

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("C4:IR30")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If

vals = Array("C", "D", "G", "H", "K", "L", "O", "S", "C", "X")
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 8, 15)

For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
icolor = nums(i)
End If
Next
If icolor 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
End Sub

--
Gary's Student


"Manj" wrote:

I have used the below code to get around the 3 conditional formats in excel.
But this only work when new values are entered. How do get this code to work
for values which I have already entered.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
If Not Intersect(Target, Range("C4:IR30")) Is Nothing Then
Select Case Target
Case "C", "c"
icolor = 8 'light blue
Case "D", "D"
icolor = 9 'brown
Case "G", "g"
icolor = 6 'yellow
Case "H", "h"
icolor = 3 'red
Case "K", "k"
icolor = 7 'pink
Case "L", "l"
icolor = 4 'green
Case "O", "o"
icolor = 20 'light blue
Case "S", "s"
icolor = 10 'dark green
Case "C", "c"
icolor = 8 'light blue
Case "X", "x"
icolor = 15 'grey
Case Else
'Whatever '1 black, 2 white, 5 dark blue, 11 dark blue
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub

More than 3 conditional formats in excel
 

Thanks Gary - This was excellent

"Gary''s Student" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("C4:IR30")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If

vals = Array("C", "D", "G", "H", "K", "L", "O", "S", "C", "X")
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 8, 15)

For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
icolor = nums(i)
End If
Next
If icolor 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
End Sub

--
Gary's Student


"Manj" wrote:

I have used the below code to get around the 3 conditional formats in excel.
But this only work when new values are entered. How do get this code to work
for values which I have already entered.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
If Not Intersect(Target, Range("C4:IR30")) Is Nothing Then
Select Case Target
Case "C", "c"
icolor = 8 'light blue
Case "D", "D"
icolor = 9 'brown
Case "G", "g"
icolor = 6 'yellow
Case "H", "h"
icolor = 3 'red
Case "K", "k"
icolor = 7 'pink
Case "L", "l"
icolor = 4 'green
Case "O", "o"
icolor = 20 'light blue
Case "S", "s"
icolor = 10 'dark green
Case "C", "c"
icolor = 8 'light blue
Case "X", "x"
icolor = 15 'grey
Case Else
'Whatever '1 black, 2 white, 5 dark blue, 11 dark blue
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub

More than 3 conditional formats in excel
 

Thanks Mike, Gary VBA code did the trick

"Mike" wrote:

This should help

http://www.microsoft.com/office/comm...7-ee3abfdb66d9

"Manj" wrote:

I have used the below code to get around the 3 conditional formats in excel.
But this only work when new values are entered. How do get this code to work
for values which I have already entered.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
If Not Intersect(Target, Range("C4:IR30")) Is Nothing Then
Select Case Target
Case "C", "c"
icolor = 8 'light blue
Case "D", "D"
icolor = 9 'brown
Case "G", "g"
icolor = 6 'yellow
Case "H", "h"
icolor = 3 'red
Case "K", "k"
icolor = 7 'pink
Case "L", "l"
icolor = 4 'green
Case "O", "o"
icolor = 20 'light blue
Case "S", "s"
icolor = 10 'dark green
Case "C", "c"
icolor = 8 'light blue
Case "X", "x"
icolor = 15 'grey
Case Else
'Whatever '1 black, 2 white, 5 dark blue, 11 dark blue
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub