Solved

excel trick, if "good" set whole row green

Posted on 2011-02-11
25
408 Views
Last Modified: 2012-05-11
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




0
Comment
Question by:TIMFOX123
  • 8
  • 6
  • 5
  • +2
25 Comments
 
LVL 22

Accepted Solution

by:
rspahitz earned 250 total points
ID: 34873085
this is a classic case for Conditional Formatting.

If using Excel 2007/2010,
So first select the row where you want this to apply.
Then Home tab, Styles group box, choose Conditional Formatting
There are a few options there but use New Rule.
Then pick the last one: Use a formula

Enter the formula (assuming applied to row 1): H1="good"
Then set the format (green background)

Add another rule for yellow.

0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
ID: 34873144
Select your whole range and then use this formula in conditional formatting

=$H1="good"

Set required format and repeat for other words

Note $ in formula - if range start on row other than 1 then change as appropriate

Regards, barry
0
 
LVL 17

Expert Comment

by:gtgloner
ID: 34873151
Have a look at the file below. In row 1, type:

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
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34873166
Note that for blank  condition use

=$H1=""

$ sign ensures that whole row is formatted
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34873184
This is via VBA. Please paste it in the worksheet selection event.

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

Open in new window


Sid
0
 

Author Comment

by:TIMFOX123
ID: 34873348
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 ?

0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34873365
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)
0
 

Author Comment

by:TIMFOX123
ID: 34873430
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)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34873445
If a vba code interests you then do have a look at the code that I posted.

Sid
0
 

Author Comment

by:TIMFOX123
ID: 34873459
It is intresting, I just do not know how to add it :)
Tell me more.
0
 

Author Comment

by:TIMFOX123
ID: 34873462
oh excel 2007
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34873470
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 :)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:TIMFOX123
ID: 34873486
If I could get it done with out vba, I would prefer that.
I just get it to work for just one column.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34873493
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
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34873498
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
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34873704
Did you use the $ in the formula?
0
 

Author Comment

by:TIMFOX123
ID: 34874356
I put that vm macro in somehow badly.  

I am sure it works, I need to undo what I have done :)

any ideas ?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34874422
Upload your file here. I will remove it :)

Sid
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34874555
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
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34874584
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
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34874760
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())="working"
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.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34875170
....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
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34875391
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
0
 

Author Comment

by:TIMFOX123
ID: 34882704
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
0
 

Author Closing Comment

by:TIMFOX123
ID: 34882716
BTW barry was the first person to tell me what I was missing  :)
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now