Excel conditional formatting row color [Solved/Closed]

Report
-
 AM14 -
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 replies

Can this be done using Conditional Formatting?
(Chaging the color of A1:H1 based on value or text in H1)
93
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3794 users have said thank you to us this month

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Yes
when you do conditonal formatting, in the condition, lock the cell address as in $A1
Excelguru,

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

Add the following code and run once
Sub changeApplicationEnableEvents2truee()
Application.EnableEvents = True
End Sub
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??
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307 > JC
In a new sub
Run only once This is to make the EnableEvents to true in the application(excel)
>
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011

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
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307 > JC
You are using Lcase and then you are using "A"cquired, "R"ejected etc
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
I mean the use of $ to lock the cell reference (Just read it twice: lock the cell address as in $A1 )
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
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
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
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
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
AusSteelMan,

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

Pedro
> Pedro
Your welcome mate.
ASM
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.
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!