Excel 2003 with 6 Conditional Formatting
 

Hello,

I have rows of data ( a1:e1, a2:e2, a3:e3, etc.) I would like to put a
letter in column (f1: f100) that would change the background color of that
row of data ( a1:e1)

I think i have to use a WorkSheet_change event. I've found the following
code but don't understand how to change the background color for the row of
data. If i put R in f1 the row a1:e1 would change to red background.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer

If Not Intersect(Target, Range("F1:F100")) is Nothing Then

Select Case Target

Case Is = R
icolor = Red

Case Is =G
icolor = Green

Case Is = B
icolor = Blue

Case Is = Y
icolor = Yellow

Case Is = M
icolor = Magenta

Case Is = C
icolor = Cyan


End Select


Target.Interior.ColorIndex = icolor

End If


End Sub

Thanks
Brian

Excel 2003 with 6 Conditional Formatting
 

Maybe

Target.entirerow.Interior.ColorIndex = icolor
(for the entire column)

or

Target.entirerow.resize(1,5).interior.colorindex = icolor

I find this a little more self-documenting:
Target.entirerow.cells(1).resize(1,5).interior.col orindex = icolor

And don't you mean vbRed or is Red declared somewhere else?
(same with the other constants, too.)

Brian wrote:

Hello,

I have rows of data ( a1:e1, a2:e2, a3:e3, etc.) I would like to put a
letter in column (f1: f100) that would change the background color of that
row of data ( a1:e1)

I think i have to use a WorkSheet_change event. I've found the following
code but don't understand how to change the background color for the row of
data. If i put R in f1 the row a1:e1 would change to red background.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer

If Not Intersect(Target, Range("F1:F100")) is Nothing Then

Select Case Target

Case Is = R
icolor = Red

Case Is =G
icolor = Green

Case Is = B
icolor = Blue

Case Is = Y
icolor = Yellow

Case Is = M
icolor = Magenta

Case Is = C
icolor = Cyan

End Select

Target.Interior.ColorIndex = icolor

End If

End Sub

Thanks
Brian

--

Dave Peterson

Excel 2003 with 6 Conditional Formatting
 

I spliced together some code that I had saved from a long time ago, and came
up with this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WatchRange As Range
Dim CellVal As String

If Target.Cells.count 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("F1:F100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Set T = Target
codes = Array("R", "G", "B", "Y", "M", "C")
valuess = Array(3, 4, 5, 6, 7, 8)
v = T.Value
For i = 0 To 5
If v = codes(i) Then
Application.EnableEvents = False
T.EntireRow.Interior.ColorIndex = valuess(i)
Application.EnableEvents = True
Exit Sub
End If
Next
End If

End Sub

It is event Code; it runs under the sheet. Basically, right-click on your
sheet and pop the code into the window that opens.

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Dave Peterson" wrote:

Maybe

Target.entirerow.Interior.ColorIndex = icolor
(for the entire column)

or

Target.entirerow.resize(1,5).interior.colorindex = icolor

I find this a little more self-documenting:
Target.entirerow.cells(1).resize(1,5).interior.col orindex = icolor

And don't you mean vbRed or is Red declared somewhere else?
(same with the other constants, too.)

Brian wrote:

Hello,

I have rows of data ( a1:e1, a2:e2, a3:e3, etc.) I would like to put a
letter in column (f1: f100) that would change the background color of that
row of data ( a1:e1)

I think i have to use a WorkSheet_change event. I've found the following
code but don't understand how to change the background color for the row of
data. If i put R in f1 the row a1:e1 would change to red background.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer

If Not Intersect(Target, Range("F1:F100")) is Nothing Then

Select Case Target

Case Is = R
icolor = Red

Case Is =G
icolor = Green

Case Is = B
icolor = Blue

Case Is = Y
icolor = Yellow

Case Is = M
icolor = Magenta

Case Is = C
icolor = Cyan

End Select

Target.Interior.ColorIndex = icolor

End If

End Sub

Thanks
Brian

--

Dave Peterson