Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting - Excel 2003 | Excel Discussion (Misc queries) | |||
conditional formatting in excel 2003 | Excel Worksheet Functions | |||
Conditional formatting in Excel 2003 | Excel Discussion (Misc queries) | |||
Conditional Formatting - Excel 2003 | Excel Discussion (Misc queries) | |||
Conditional formatting excel 2003 | Excel Worksheet Functions |