Solved: VB solution to conditional row formating

[mrvain]

I hate asking for help, but I can?t find an answer to this.

I need a VB solution to shading and coloring text in a portion of a row based on a cell entry.

Example:

A5 through F5 turn green with red text if G5 is ?keyword?
H5 through J5 turn blue with yellow text if K5 is < 0

This must be done in VB. I can?t use the excel conditional formatting feature because I have other things going on taking up the limit of 3.
I am not proficient in VB though I know a few other languages. Any tips would be appreciated.

Thanks in advance!

[Bob Phillips]

[

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

On Error GoTo ws_exit
Application.EnableEvents = False
If Target.Address = "$G$5" Then
If Target.Value = "keyword" Then
Range("A5:F5").Interior.ColorIndex = 4
Else
Range("A5:F5").Interior.ColorIndex = xlColorIndexNone
End If
ElseIf Target.Address = "$K$5" Then
If Target.Value < 0 Then
Range("A5:F5").Interior.ColorIndex = 5
Range("A5:F5").Font.ColorIndex = 6
Else
Range("H5:J5").Interior.ColorIndex = xlColorIndexNone
Range("H5:J5").Font.ColorIndex = xlColorIndexAutomatic
End If
End If

ws_exit:
Application.EnableEvents = True

End Sub


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.

[mrvain]

Perfect! Thanks a million.

[sbrenner]

The VB code here is very close to what I need, but I could use some help tweaking it a bit to get exactly what I'm looking for.

I've got a number in column A and data in each row from columns B through F. Depending on the value of the number in column A, the *entire* row should be formatted (preferrably the background color, font color, and cell border color). I need this formatting to apply (consistently) throughout the entire sheet.

Any way to do this?

Thanks!

[mdmackillop]

Hi, and welcome to VBAX
Add your own criteria and formatting into the following
Regards
MD


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
On Error GoTo ws_exit
Application.EnableEvents = False
With Target.EntireRow
Select Case Target
Case 1
.Interior.ColorIndex = 4
.Font.ColorIndex = 6
Case 2
.Interior.ColorIndex = 8
.Font.ColorIndex = 12
End Select
End With
ws_exit:
Application.EnableEvents = True
End Sub

[sbrenner]

Thanks so much! That really helped. There's just one more thing (isn't there always?!)...

The VB code seems to work fine as long as I manually enter the value in column A. And if I manually change the value, the row color changes properly. But I actually want to get the value in column A by adding the values in two other columns (e.g. In 'A1', I've got "=C1+E1") and *those* columns get *their* values based on text data in yet two more columns (e.g. In 'C1', I've got "=IF(B1="high",40,IF(B1="med",30,IF(B1="low",5,0)))"

It seems like the row color formatting doesn't work when the values change due to the "subordinate" values in other columns changing. Am I doing something wrong?

If there's no way to get it work this way, perhaps simplifying what I'm really trying to do would be easier...

All I need is code that behaves with this logic:

If column X is value1 and column Y is value 100
make the entire row background color1
else if column X is value 2 and column Y is value 100
make the entire row background color2
else if column X is value 3 and column Y is value 100
make the entire row background color3
else if column X is value 1 and column Y is value 200
make the entire row background color4
else if column X is value 2 and column Y is value 200
make the entire row background color 5
else if column X is value 3 and column Y is value 200
make the entire row background color6
else if column X is not blank
make the entire row background color7
else
make the entire row background whatever the default color is

And, most importantly, the row color must change any time either (or both) of the values in columns X or Y change. Is this easier to achieve?

[mdmackillop]

The attached workbook is based on something I made up a while ago. The code "remembers" the sum total of column A when the sheet is activated, if this changes, the code is run. Change the values in the yellow cells to produce a 1 or 2 in column A (A1 = D1 + E1)

[mrvain]

This is not as efficient as I would like but it may work by using Offset.
Will color 9 cells to the right if either of these conditions are met:

colors blue if......[cell A = blue AND cell B = 100]
colors red if.......[cell A = red AND cell B = 200]
colors orange if..[cell A = orange AND cell B = 300]

I was having problems as you mentioned before with it not instantaneously changing.
Not sure at the moment how to correct this.

FYI:
To change the color of the font you can use ".Font.ColorIndex = 23" instead of ".Interior.ColorIndex = 23".
Font can also be made bold with "Cell.Font.Bold = True".
You can also use "Offset(0, -1)" to reference cells to the left.


Private Sub Worksheet_Change(ByVal Target As Range)
ws_exit:
Application.EnableEvents = True

If Intersect(Target, [A1:A100]) Is Nothing Then
Exit Sub
Else
For Each Cell In Range("A1:A100")
If Cell = "blue" And Cell.Offset(0, 1) = "100" Then
Cell.Font.ColorIndex = 23
Cell.Offset(0, 1).Interior.ColorIndex = 23
Cell.Offset(0, 2).Interior.ColorIndex = 23
Cell.Offset(0, 3).Interior.ColorIndex = 23
Cell.Offset(0, 4).Interior.ColorIndex = 23
Cell.Offset(0, 5).Interior.ColorIndex = 23
Cell.Offset(0, 6).Interior.ColorIndex = 23
Cell.Offset(0, 7).Interior.ColorIndex = 23
Cell.Offset(0, 8).Interior.ColorIndex = 23
Cell.Offset(0, 9).Interior.ColorIndex = 23
ElseIf Cell = "red" And Cell.Offset(0, 1) = "200" Then
Cell.Font.ColorIndex = 53
Cell.Offset(0, 1).Interior.ColorIndex = 53
Cell.Offset(0, 2).Interior.ColorIndex = 53
Cell.Offset(0, 3).Interior.ColorIndex = 53
Cell.Offset(0, 4).Interior.ColorIndex = 53
Cell.Offset(0, 5).Interior.ColorIndex = 53
Cell.Offset(0, 6).Interior.ColorIndex = 53
Cell.Offset(0, 7).Interior.ColorIndex = 53
Cell.Offset(0, 8).Interior.ColorIndex = 53
Cell.Offset(0, 9).Interior.ColorIndex = 53
ElseIf Cell = "orange" And Cell.Offset(0, 1) = "300" Then
Cell.Font.ColorIndex = 45
Cell.Offset(0, 1).Interior.ColorIndex = 45
Cell.Offset(0, 2).Interior.ColorIndex = 45
Cell.Offset(0, 3).Interior.ColorIndex = 45
Cell.Offset(0, 4).Interior.ColorIndex = 45
Cell.Offset(0, 5).Interior.ColorIndex = 45
Cell.Offset(0, 6).Interior.ColorIndex = 45
Cell.Offset(0, 7).Interior.ColorIndex = 45
Cell.Offset(0, 8).Interior.ColorIndex = 45
Cell.Offset(0, 9).Interior.ColorIndex = 45
Else: Cell.Interior.ColorIndex = 0

End If
Next Cell
End If
End Sub

[mdmackillop]

Hi mrvain,
Instead of the 9 offset lines, you can use

Cell.Range("B1:J1").Interior.ColorIndex = 23

Also note you are saying
If Cell.offset = "100" in this case 100 is a string, not a number.
Regards
MD

[sbrenner]

Wow. I've spent more time with Visual Basic than I ever wanted to! But with the code provided here, and some experimentation of my own, I've gotten to 99.9% of what I was trying to achieve. Here's the code:

Private Sub Worksheet_Change(ByVal Target As Range)
ws_exit:
Application.EnableEvents = True

If Intersect(Target, [B2:B500]) Is Nothing Then
Exit Sub
Else
For Each Cell In Range("B2:B500")

If Cell = "high" And Cell.Offset(0, 2) = "yes" Then
Cell.Font.ColorIndex = 0
Cell.Offset(0, -1).Interior.ColorIndex = 38
Cell.Range("A1:J1").Interior.ColorIndex = 38
ElseIf Cell = "med" And Cell.Offset(0, 2) = "yes" Then
Cell.Font.ColorIndex = 0
Cell.Offset(0, -1).Interior.ColorIndex = 35
Cell.Range("A1:J1").Interior.ColorIndex = 35
ElseIf Cell = "low" And Cell.Offset(0, 2) = "yes" Then
Cell.Font.ColorIndex = 0
Cell.Offset(0, -1).Interior.ColorIndex = 19
Cell.Range("A1:J1").Interior.ColorIndex = 19

ElseIf Cell = "high" And Cell.Offset(0, 2) = "no" Then
Cell.Font.ColorIndex = 0
Cell.Offset(0, -1).Interior.ColorIndex = 24
Cell.Range("A1:J1").Interior.ColorIndex = 24
ElseIf Cell = "med" And Cell.Offset(0, 2) = "no" Then
Cell.Font.ColorIndex = 0
Cell.Offset(0, -1).Interior.ColorIndex = 36
Cell.Range("A1:J1").Interior.ColorIndex = 36
ElseIf Cell = "low" And Cell.Offset(0, 2) = "no" Then
Cell.Font.ColorIndex = 0
Cell.Offset(0, -1).Interior.ColorIndex = 15
Cell.Range("A1:J1").Interior.ColorIndex = 15

ElseIf Cell = "done" Then
Cell.Offset(0, 2) = "done"
Cell.Font.ColorIndex = 0
Cell.Range("A1:J1").Font.Italic = True
Cell.Offset(0, -1).Font.Italic = True
Cell.Offset(0, -1).Interior.ColorIndex = 0
Cell.Range("A1:J1").Interior.ColorIndex = 0

Else: Cell.Offset(0, -1).Interior.ColorIndex = 0
Cell.Range("A1:J1").Interior.ColorIndex = 0

End If
Next Cell
End If
End Sub


You'll note I changed a bunch of stuff. I realized that column B is what I really need to drive the colors from, not column A. I set the font color to black for now, but left the code in so I'll remember how to change it later if I ever want to. I also added another condition where the value is "done". In that case, I don't care what column D is so I didn't need the "and". And I decided to get a little fancy with that condition and change the font style instead of the fill color. (There's a minor bug, which I won't bother fixing: If I enter "done" in column D and cause the font to go italic, then change "done" to another valid value, the font *stays* italic. I realize I could add code to revert it back, but for my purposes, once I mark something "done", it's never going to change.)

Here's a screenshot:

http://www.scottbrenner.us/misc/excel.gif

There's a minor problem remaining, but it's not that big of a deal. If I change a value in column D, the color doesn't change until I change / enter something in column B (doesn't matter what or where, as long as something gets entered.)

I've heard that Excel 2007 will allow more than 3 types of conditional formatting. But now I'll appreciate that *so* much more. Thanks for all your help!