CONDITIONAL FORMATING OR OBJECT MODULE ?

[YANECKC]

MY SHEET1 HAS 400 ROWS AND 8 COLUMNS.
I WANT TO COLOR THE 8 ENTIRE COLUMNS OF A ROW
DEPENDING ON THE DATA FROM THE CELL IN COLUMN 1. MEANING IF A LETTER B IS IN COLUMN 1 OF ROW 1 IT WOULD COLOR THE 8 COLUMNS IN ROW 1
BLUE. IF A LETTER R WAS IN COLUMN 1 OF ROW 1 IT WOULD COLOR THE 8 COLUMNS IN ROW 1 RED.

COLOR CODE LIST
B=BLUE
P=PINK
Y=YELLOW
R=RED
G=GREEN
X=BLACK
O=ORGANGE

I KNOW HOW TO CONDITIONAL FORMAT THE FIRST COLUMN WITH COLORS. HOWEVER THATS ONLY THE
FIRST COLUMN. WHAT IS THE BEST WAY TO DO ALL
8 COLUMNS AT ONCE DEPENDING WHAT WAS IN THE FIRST COLUMN.

THANK YOU FOR YOUR ANTICIPATED RESPONSE.

YANECKC

 

[Tom Urtis]

Try this VBA approach instead of conditional formatting, given your 8 conditions (7 colors plus nothing if no such letter code is entered).

Right click on your sheet tab, left click on View Code, and paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column<> 1 Or Target.Row > 400 Or Target.Cells.Count > 1 Then Exit Sub
Dim r As Variant
Set r = Range(Target, Target.Offset(0, 7)).Interior
Select Case Target.Value
Case "B"
r.ColorIndex = 8
Case "P"
r.ColorIndex = 7
Case "Y"
r.ColorIndex = 6
Case "G"
r.ColorIndex = 4
Case "R"
r.ColorIndex = 3
Case "X"
r.ColorIndex = 1
Case "O"
r.ColorIndex = 46
Case Else
r.ColorIndex = 0
End Select
End Sub
_________________
Tom Urtis
This message was edited by Tom Urtis on 2002-09-24 20:53

 

[Derek]

hi there
Or if you want to use conditional formatting use this event macro. (Not as neat as Tom's but seems to work okay)

Private Sub Worksheet_Change(ByVal Target As Range)
If Cells(Target.Row, 1).Value = "B" Then
With Range(Target, Target.Offset(0, 7))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 41
End With

Else
If Cells(Target.Row, 1).Value = "P" Then
With Range(Target, Target.Offset(0, 7))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 38
End With

Else
If Cells(Target.Row, 1).Value = "Y" Then
With Range(Target, Target.Offset(0, 7))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 6
End With

Else
If Cells(Target.Row, 1).Value = "R" Then
With Range(Target, Target.Offset(0, 7))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 3
End With

Else
If Cells(Target.Row, 1).Value = "G" Then
With Range(Target, Target.Offset(0, 7))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 35
End With

Else
If Cells(Target.Row, 1).Value = "X" Then
With Range(Target, Target.Offset(0, 7))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 1
End With

Else
If Cells(Target.Row, 1).Value = "O" Then
With Range(Target, Target.Offset(0, 7))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 46
End With
End If
End If
End If
End If
End If
End If
End If

End Sub

regards
Derek

 

[Derek]

I really liked Tom's post so I tried to adapt it for conditional formatting, and came up with this, which is better than my original effort.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim z As Byte
If Target.Column<> 1 Or Target.Row > 400 Or Target.Cells.Count > 1 Then
Exit Sub
Else
Select Case Target.Value
Case "B"
z = 8
Case "P"
z = 7
Case "Y"
z = 6
Case "G"
z = 4
Case "R"
z = 3
Case "X"
z = 1
Case "O"
z = 46
Case Else
z = 0
End Select

If z = 0 Then
Range(Target, Target.Offset(0, 7)).FormatConditions.Delete
Else
With Range(Target, Target.Offset(0, 7))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = z
End With
End If
End If
End Sub

regards
Derek
This message was edited by Derek on 2002-09-24 20:20

 

[Derek]

Or, you could just enter a number instead of a letter and have a full 55 colour range...........

Private Sub Worksheet_Change(ByVal Target As Range)
Dim z As Byte
If Target.Column <> 1 Or Target.Row > 400 Or Target.Cells.Count > 1 Or Target.Value > 55 Then
Exit Sub
Else
z = Target.Value
If z = 0 Then
Range(Target, Target.Offset(0, 7)).FormatConditions.Delete
Else
With Range(Target, Target.Offset(0, 7))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = z
End With
End If
End If
End Sub

It gets more and more fun
regards
Derek