TIMFOX123
asked on
excel trick, if "good" set whole row green
We have a status line in excel and the boss is spending a lot of time formatting and reformatting.
status is a columb, lets call it "H"
If the status = good, format whole row green
If the status = working, format whole row yellow
If the status = blank, format whole row white.
thank you
status is a columb, lets call it "H"
If the status = good, format whole row green
If the status = working, format whole row yellow
If the status = blank, format whole row white.
thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Note that for blank condition use
=$H1=""
$ sign ensures that whole row is formatted
=$H1=""
$ sign ensures that whole row is formatted
This is via VBA. Please paste it in the worksheet selection event.
Sid
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("H:H")) Is Nothing Then
Select Case UCase(Target.Value)
Case "GOOD"
Rows(Target.Row).Interior.ColorIndex = 4
Case "WORKING"
Rows(Target.Row).Interior.ColorIndex = 6
Case Else
Rows(Target.Row).Interior.ColorIndex = xlAutomatic
End Select
End If
Application.EnableEvents = True
End Sub
Sid
ASKER
Cool except I can make this work for one cell
if cell = good then format whole row green.
Now, how do I "copy" this conditional formating to many many cells ?
if cell = good then format whole row green.
Now, how do I "copy" this conditional formating to many many cells ?
Copy the cell (Ctrl C) with the conditional formatting
Select the row
Right-click and in the paste section, choose formatting (with the % and paintbrush)
Select the row
Right-click and in the paste section, choose formatting (with the % and paintbrush)
ASKER
tried this several time but I can not get it to format the other rows
Copy the cell (Ctrl C) with the conditional formatting
Select the row
Right-click and in the paste section, choose formatting (with the % and paintbrush)
Copy the cell (Ctrl C) with the conditional formatting
Select the row
Right-click and in the paste section, choose formatting (with the % and paintbrush)
If a vba code interests you then do have a look at the code that I posted.
Sid
Sid
ASKER
It is intresting, I just do not know how to add it :)
Tell me more.
Tell me more.
ASKER
oh excel 2007
When you copy, make sure the active cell is the one with the conditional formatting (the =$H1="good")
However, I find it easier to just select the whole row then apply the conditional formatting formula.
Also, make sure you apply the green formatting :)
However, I find it easier to just select the whole row then apply the conditional formatting formula.
Also, make sure you apply the green formatting :)
ASKER
If I could get it done with out vba, I would prefer that.
I just get it to work for just one column.
I just get it to work for just one column.
Sure.
Press Alt+F11 from the worksheet to open the VBE Editor and then paste the code as in the snapshot :)
Hope this helps.
Sid
Untitled.jpg
Press Alt+F11 from the worksheet to open the VBE Editor and then paste the code as in the snapshot :)
Hope this helps.
Sid
Untitled.jpg
If I could get it done with out vba, I would prefer that.
I just get it to work for just one column.
In that case follow what Barry says :)
Sid
Did you use the $ in the formula?
ASKER
I put that vm macro in somehow badly.
I am sure it works, I need to undo what I have done :)
any ideas ?
I am sure it works, I need to undo what I have done :)
any ideas ?
Upload your file here. I will remove it :)
Sid
Sid
See attached - example
this uses the method I suggested to highlight the whole row (or at least the part I selected). If you can't make it work I can talk you through the steps.
regards, barry
PS which Excel version are you using?
26815396.xls
this uses the method I suggested to highlight the whole row (or at least the part I selected). If you can't make it work I can talk you through the steps.
regards, barry
PS which Excel version are you using?
26815396.xls
I would suggest that you upload the file here so that I or Barry can remove the macro and then you can follow Barry which will be very easy :)
Sid
Sid
Let's try once more...first, revert to a saved copy.
On the sheet where you want this stuff to apply, select the whole page (Ctrl+A)
Proceed to Conditional Formatting, add new rule, enter a formula, as explained previously.
enter this formula:
=INDIRECT("H"&ROW())="good "
then format to show a green background.
then add another rule to the above:
=INDIRECT("H"&ROW())="work ing"
then format to show a yellow background.
that's it!
Note that applying all of these conditional formatting settings to the whole workbook will cause it to be a big file so you may want to select just a few rows where it will apply instead of the entire sheet.
On the sheet where you want this stuff to apply, select the whole page (Ctrl+A)
Proceed to Conditional Formatting, add new rule, enter a formula, as explained previously.
enter this formula:
=INDIRECT("H"&ROW())="good
then format to show a green background.
then add another rule to the above:
=INDIRECT("H"&ROW())="work
then format to show a yellow background.
that's it!
Note that applying all of these conditional formatting settings to the whole workbook will cause it to be a big file so you may want to select just a few rows where it will apply instead of the entire sheet.
....but you don't need INDIRECT or ROW() functions, you don't need any functions......
as I suggested previously this formula is sufficient assuming you are starting at row 1
=$H1="good"
If you want to start at a different row then you can adjust the formula to that row, e.g. if you want to format all columns from A to Z from row 4 to row 100 then you should select that range, A4:Z100 [ you can select that range easily by just typing it into the box above A1 and pressing ENTER]
Now you just apply the formula that applies to the top row of the range
=$H4="good"
by using the $ you are "fixing" the column part of the cell reference. This means that all columns in the range will reference column H....but the rows will adjust so cells in row 5 or row 20 will use the H cell in the current row.
regards, barry
as I suggested previously this formula is sufficient assuming you are starting at row 1
=$H1="good"
If you want to start at a different row then you can adjust the formula to that row, e.g. if you want to format all columns from A to Z from row 4 to row 100 then you should select that range, A4:Z100 [ you can select that range easily by just typing it into the box above A1 and pressing ENTER]
Now you just apply the formula that applies to the top row of the range
=$H4="good"
by using the $ you are "fixing" the column part of the cell reference. This means that all columns in the range will reference column H....but the rows will adjust so cells in row 5 or row 20 will use the H cell in the current row.
regards, barry
agreed...i think barry should get the points
not sure why it wasn't working for timfox...maybe upload a file minus important data and we can see if there's something strange about it
not sure why it wasn't working for timfox...maybe upload a file minus important data and we can see if there's something strange about it
ASKER
All:
You all are my heros !! great job.
the reason the non vbscript was not working for me is I had to triple read the instuctions. you need to highlight the cells you want conditionally formatted before you go into the wizzard and write the macro. As usuall it was user error Pbkac ( problem between keyboard and chair ).
thank you alll
You all are my heros !! great job.
the reason the non vbscript was not working for me is I had to triple read the instuctions. you need to highlight the cells you want conditionally formatted before you go into the wizzard and write the macro. As usuall it was user error Pbkac ( problem between keyboard and chair ).
thank you alll
ASKER
BTW barry was the first person to tell me what I was missing :)
good
in any cell and watch the background change. This is an example of use of conditional formatting. If you want to see how this was achieved, select one of the cells in row 1 and click Format-Conditional Formatting in the menu bar at the top of the screen. You will see the 3 different colours depending on the condition.
Book1.xls