Printable View
I'm working in Excel 2002. Is there a way with vba to format and entire row based on the contents of both columns C and D.
If C = "A" and D = "E" then the row color would be 40, light orange and the text bold in black 1.
If C = "A" and D = "P" then the row color would be 35, light green and the text bold in dark red 9.
If C = "X" and D = "E" then the row color would be 39, purple with no text formatting.
I plan on having more than 3 instances so Format>Conditional Formatting won't work.
[vba]
'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "C:C,D:D" '<=== change to suit
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case True
Case Me.Cells(.Row, "C").Value = "A" And Me.Cells(.Row, "D").Value = "E"
Me.Rows(.Row).Interior.ColorIndex = 40
Me.Rows(.Row).Font.ColorIndex = xlColorIndexAutomatic
Me.Rows(.Row).Font.Bold = True
Case Me.Cells(.Row, "C").Value = "A" And Me.Cells(.Row, "D").Value = "P"
Me.Rows(.Row).Interior.ColorIndex = 35
Me.Rows(.Row).Font.ColorIndex = 9
Me.Rows(.Row).Font.Bold = True
Case Me.Cells(.Row, "C").Value = "X" And Me.Cells(.Row, "D").Value = "E"
Me.Rows(.Row).Interior.ColorIndex = 39
Me.Rows(.Row).Font.ColorIndex = 39
Me.Rows(.Row).Font.Bold = False
Case Else
Me.Rows(.Row).Interior.ColorIndex = xlColorIndexNone
Me.Rows(.Row).Font.ColorIndex = xlColorIndexAutomatic
Me.Rows(.Row).Font.Bold = False
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
[/vba]
This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.
Fantastic! This works great. Thanks so much.
Can this be extended to look at a third column (K) with a date?
If K=11/10/08 or 11/11/08 (today or tomorrow) then the row color is 46.
If K=11/9/08 (yesterday) then the row color is 9.
If K=11/12/08 or greater then the row color defaults to what C and D dictate.
K would only change row colors if C = "A" or "H".
If I should have started a new thread with this questions, just let me know. Thanks in advance.
No, best to keep this same thread, if gives continuity.
Can you give a full truth table for all options to avoid a round of questions?
I've attached a spreadsheet outlining the 3 cells and different row and font formats.
Column K overrides the formatting any time an "A" of a "H" is placed in column C. If the date is yesterday, K makes the row a dark red. Today or tomorrow in K makes the row bright orange. If K is the day after tomorrow or beyond, the formatting reverts back to what the letter combinations in C & D dictate.
If C has "C" or "X", then K does not override the formats.
I've tried playing around with the code you gave me and below is what I've come up with. (How did you get your code to come up in that nice clean box?) But I'm not sure that this will update the rows by itself. If I manually change the date in K to yesterday it changes the row to dark red but I'm looking for this to update the row colors without any user input. At least as far as K is concerned.
=TODAY() is the formula in A1 if that helps to include in the code.
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C:C,D:D,K:K" '<=== change to suit
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case True
Case Me.Cells(.Row, "C").Value = "A" And Me.Cells(.Row, "D").Value = "E" And Me.Cells(.Row, "K").Value < Date
Me.Rows(.Row).Interior.ColorIndex = 9
Me.Rows(.Row).Font.ColorIndex = 2
Me.Rows(.Row).Font.Bold = True
Me.Rows(.Row).Font.Strikethrough = False
Case Me.Cells(.Row, "C").Value = "A" And Me.Cells(.Row, "D").Value = "E" And Me.Cells(.Row, "K").Value = Date
Me.Rows(.Row).Interior.ColorIndex = 46
Me.Rows(.Row).Font.ColorIndex = 1
Me.Rows(.Row).Font.Bold = False
Me.Rows(.Row).Font.Strikethrough = False
Case Me.Cells(.Row, "C").Value = "A" And Me.Cells(.Row, "D").Value = "E" And Me.Cells(.Row, "K").Value = Date + 1
Me.Rows(.Row).Interior.ColorIndex = 46
Me.Rows(.Row).Font.ColorIndex = 1
Me.Rows(.Row).Font.Bold = False
Me.Rows(.Row).Font.Strikethrough = False
Case Me.Cells(.Row, "C").Value = "A" And Me.Cells(.Row, "D").Value = "E"
Me.Rows(.Row).Interior.ColorIndex = 40
Me.Rows(.Row).Font.ColorIndex = 1
Me.Rows(.Row).Font.Bold = False
Me.Rows(.Row).Font.Strikethrough = False[/VBA]
Any ideas? xld? Anyone?
You are missing some of the closing rows to the code, but then by going back to XLD's code you can find these.
Thanks for responding. I only posted the first part of the code due to length. Any ideas on making the code dynamic for column K?