Excel conditional formatting row color
Solved/Closed
Related:
- Excel conditional formatting row color
- Excel date format dd.mm.yyyy - Guide
- Saints row 2 cheats - Guide
- Marksheet format in excel - Guide
- Notepad++ background color - Guide
- Clear formatting in excel - Guide
6 responses
Can this be done using Conditional Formatting?
(Chaging the color of A1:H1 based on value or text in H1)
(Chaging the color of A1:H1 based on value or text in H1)
Excelguru
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Jun 14, 2009 at 08:40 AM
Jun 14, 2009 at 08:40 AM
Yes
when you do conditonal formatting, in the condition, lock the cell address as in $A1
when you do conditonal formatting, in the condition, lock the cell address as in $A1
Excelguru
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
May 14, 2009 at 02:41 AM
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
Application.EnableEvents =false
Add the following code and run once
Sub changeApplicationEnableEvents2truee()
Application.EnableEvents = True
End Sub
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
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)
Run only once This is to make the EnableEvents to true in the application(excel)
JC
>
Excelguru
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
Aug 21, 2009 at 01:40 PM
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
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
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
Aug 22, 2009 at 10:13 AM
You are using Lcase and then you are using "A"cquired, "R"ejected etc
Excelguru
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Jun 27, 2009 at 08:13 AM
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 )
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
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
Do use of $ to lock the cell reference (column ID only as in $A1 ) to work for a row based on a cell value
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
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
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.
For example I want to highlight each row in a table where the cells in column G are not empty.
Thank you.