Conditional Formating Question


D

DKlecka

Okay, here's what I want to do - but can't find out how to do it.

Say I have data in cells A1:A5, B1:B:5, C1:C5, D1:D5, E1:E5.

I want to apply a conditional format to the values in cells A1:A5.
Say, if the number is less then 5, then make it green. I would
ideally like to apply the same format to the entire row. So, if cell
value in A3 was 2, then B3, C3, D3 & E3 would also be green.

Is there an easy way to accomplish this?
 
Ad

Advertisements

L

Luke M

Input this formula into the first cell, A1

=IF($A1<5,true,false)

Then copy, paste special - formatting to the other cells. The dollar sign
will keep all the cells looking at the A column, but adjusting to the proper
row.
 
F

FSt1

hi
select A2 then call up conditional fomating
2003 on the menu bar....format>conditional format...
formula is.......=$A2<5.....pick your format.
note the absolute reference on the A column and relative reference to the row.
copy and paste special formats across
copy and paste special formats down.

done.

regards
FSt1
 
T

T. Valko

Try this...

Select your entire range A1:E5

In Excel versions 2003 and earlier

Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=$A1<5
Click the Format button
Select the desired style(s)
OK out

In Excel 2007

Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a
formula to determine which cells to format
Enter this formula in the box below:
=$A1<5
Click the Format button
Select the desired style(s)
OK out
 
D

DonkeyKong

=$A1<5
You don't need the IF(...,true,false)
--
David Biddulph








- Show quoted text -

Okay - maybe I need to elaborate some more - as I'm struggling here...

What I'm using this for is an expense tracker...kind of like a
checkbook register...

So, in A7 - A100 could be either check numbers or other items. But
what will be there is the letter "P". So, say, in A7, I have "P", B7
is the date of the expense, C7 is the description and D7 the amount
and E7 is the overall running total of the expenses.
Now, I have the conditional format that says if cells in A7 - A100
equals "P", then to highlight the cell green. But I also want cells
B7 through E7 to copy the same format. SO if I type in a P in say,
A25, then B25 through E25 will turn green. If something else is
there, then it removes the format...

Does that help?
 
Ad

Advertisements

T

T. Valko

What version of Excel are you using?

For Excel 2003 and earlier versions follow these steps *exactly*

Select the *entire* range of cells you want to format. If that range is
A7:E100, select cell A7 first then drag across to E7 then down to row 100.
Cell A7 will be the active cell. The active cell is the one in the selected
range that is not shaded. All other cells in the selected range will be
shaded. Another way to select the range, click inside the name box, that
little space immediately above the column header for column A and to the far
left of the formula bar where it shows you what cell is selected, type in
A7:E100 then hit ENTER. A7 will be the active cell.

With the range selected...
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula *exactly as shown* in the little box on the right:

=$A7="P"

Click the Format button
Select the Patterns tab
Select a shade of GREEN
OK your way out


--
Biff
Microsoft Excel MVP


=$A1<5
You don't need the IF(...,true,false)
--
David Biddulph








- Show quoted text -

Okay - maybe I need to elaborate some more - as I'm struggling here...

What I'm using this for is an expense tracker...kind of like a
checkbook register...

So, in A7 - A100 could be either check numbers or other items. But
what will be there is the letter "P". So, say, in A7, I have "P", B7
is the date of the expense, C7 is the description and D7 the amount
and E7 is the overall running total of the expenses.
Now, I have the conditional format that says if cells in A7 - A100
equals "P", then to highlight the cell green. But I also want cells
B7 through E7 to copy the same format. SO if I type in a P in say,
A25, then B25 through E25 will turn green. If something else is
there, then it removes the format...

Does that help?
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top