Excel conditional formatting row color

Solved/Closed
Katsu - May 13, 2009 at 09:53 AM
 AM14 - Mar 2, 2010 at 10:43 AM
Hello,
I am trying to get Excel to automatically change the color of a row depending on the entry in column H. I've got some VBA code written up, but I can't seem to get it to work. Am I making a programming mistake, or is it something else?

here's the code:
Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 8 Then Exit Sub 'Column 8 is column H (Status)
If Target.Row < 13 Then Exit Sub 'Row 13 is the first row of data
Application.EnableEvents = False
Select Case LCase(Target.Value) 'I dont know what case user will input data in
Case "maps issued"
Target.EntireRow.Interior.ColorIndex = 35
Case "complete"
Target.EntireRow.Interior.ColorIndex = 35
Case "confirmed"
Target.EntireRow.Interior.ColorIndex = 36
Case "waiting on team"
Target.EntireRow.Interior.ColorIndex = 40
Case "no show"
Target.EntireRow.Interior.ColorIndex = 22
Case "team cancelled"
Target.EntireRow.Interior.ColorIndex = 22
Case "sent up"
Target.EntireRow.Interior.ColorIndex = 36
Case Else
Target.EntireRow.Interior.ColorIndex = xlColorIndexAutomatic
End Select
Application.EnableEvents = True 'should be part of Change macro
End Sub

--Thanks

6 responses

Can this be done using Conditional Formatting?
(Chaging the color of A1:H1 based on value or text in H1)
93
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Jun 14, 2009 at 08:40 AM
Yes
when you do conditonal formatting, in the condition, lock the cell address as in $A1
18
Excelguru,

I was wondering how to lock the cell address with conditionalformatting?
0
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
May 14, 2009 at 02:41 AM
Remove the line
Application.EnableEvents =false

Add the following code and run once
Sub changeApplicationEnableEvents2truee()
Application.EnableEvents = True
End Sub
7
I deleted the line "Application.EnableEvents=False" in the code of the Post/Question, but when you say " Add The following code....." did you want this new code in a new sub or placed in the deleted code??
0
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307 > JC
Aug 21, 2009 at 11:47 AM
In a new sub
Run only once This is to make the EnableEvents to true in the application(excel)
0
JC > Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011
Aug 21, 2009 at 01:40 PM
Thanks for the fast response. I added the sub and ran the macro, but nothing happened. In addition, when I ran the Worksheet_Change macro I got an error "argument not optional" I don't have any experience with macro's and very little with VB, but usually my logic comes through...not this time I guess. I have 8 columns of data starting in row 4 and in column 7 is a validation list with 4 options (status options) What I want to do is change the background color of each row (Starting with 4, but only through Column 1:8) to correspond to the option selected in the validation list. Example: When I select "Acquired" in the Column 7 drop down list, I want the cooresponding row (not entire row, just column 1 through 8) to turn the color I assign to "Acquire" (which is green) The code I used (which I pasted below) makes sence to me as a workable macro, but excel doesn't agree with me. I initiated the macro by opening VB, Adding a module, entering the code (as shown below) closing the VB window then running once....changeApplicationEnableEvents2truee in the appropriate excel workbook. When nothing happened I tried running once.....worksheet_change in the appropriate excel worksheet and I got the error message I mentioned above.

So I ask, where did I go wrong? and is there a solution or another avenue I can attemp like coditional formatting. I do have 4 colors which is no good for excel 2003, maybe there is a work around. I don't know conditional formating so if that is the way to go, what do I do...use "cell value is or formula is" and if cell value is text, how do I manage that and also include A4:H4 of the row. If I use a formula, what is it?

I know this is alot of information and questions, you don't have to respond if you don't want to, but It would be great if you did and I beileve it can't hurt to ask. Thanks Here is the code I mentioned:
____________________________________________________________________________
Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 7 Then Exit Sub 'Column 7 is column G (Status)
If Target.Row < 4 Then Exit Sub 'Row 4 is the first row of data
Select Case LCase(Target.Value) 'I dont know what case user will input data in
Case "maps issued"
Target.EntireRow.Interior.ColorIndex = 3
Case "Declined"
Target.EntireRow.Interior.ColorIndex = 4
Case "Acquired"
Target.EntireRow.Interior.ColorIndex = 5
Case "Not Acquired"
Target.EntireRow.Interior.ColorIndex = 6
Case "Pending"
Target.EntireRow.Interior.ColorIndex = xlColorIndexAutomatic
End Select
Application.EnableEvents = True 'should be part of Change macro
End Sub
__________________________________________________________________________
Sub changeApplicationEnableEvents2truee()
Application.EnableEvents = True
End Sub
0
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307 > JC
Aug 22, 2009 at 10:13 AM
You are using Lcase and then you are using "A"cquired, "R"ejected etc
0
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Jun 27, 2009 at 08:13 AM
I mean the use of $ to lock the cell reference (Just read it twice: lock the cell address as in $A1 )
7
I am trying to use the condtional formula to hightlight the row if say H cell contains a value greater than 12.
but I cant getit going because the H cell has formula referring to the other sheet. please advice
0

Didn't find the answer you are looking for?

Ask a question
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Oct 28, 2009 at 05:30 AM
Conditional formatting checks only the value in a cell and NOT the formula in the cell

Do use of $ to lock the cell reference (column ID only as in $A1 ) to work for a row based on a cell value
5
I have dates in column M and Column N. I want each row to to change color if either Column M or Column N has a value = to today.

I need help with the syntax.

Thanks so much,

Pedro
0
Try this.

Highlight your rows (say 10 to 20)
Goto the conditional format box
Set the control to "Formula is"
Enter this formula: =OR($M10=TODAY(),$N10=TODAY())
Set your colour / font etc as desired and press Enter/OK

Hope it helped Pedro.
ASM
4
AusSteelMan,

You are a genius! Worked perfectly. Thanks so much.

Pedro
0
AusSteelMan > Pedro
Feb 3, 2010 at 04:06 PM
Your welcome mate.
ASM
0
How about if I want to highlight a whole row if a cell in that row has something in it (in other words is not empty). What would be in that cell is text, not a number. And it's not always the same text.

For example I want to highlight each row in a table where the cells in column G are not empty.

Thank you.
0
Thank you! I was just googling for a solution to my excel problem and found this fourm. I never knew before how to incorporate OR or AND into conditional formatting. thanks again!
0