# CONDITIONAL FORMATING OR OBJECT MODULE ?

#### YANECKC

##### Board Regular
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

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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

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(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(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(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(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(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(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(1).Interior.ColorIndex = 46
End With
End If
End If
End If
End If
End If
End If
End If

End Sub

regards
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(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

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(1).Interior.ColorIndex = z
End With
End If
End If
End Sub

It gets more and more fun
regards
Derek

Replies
1
Views
302
Replies
5
Views
128
Replies
6
Views
195
Replies
7
Views
176
Replies
1
Views
101

1,203,757
Messages
6,057,164
Members
444,909
Latest member
Shambles111

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back