How is it doing that? (Excel 2000)

Topic

New Reply

I have a template that was a hand me down. The ‘Status’ colum has three options [O, C, D] (Arrow, drop down). Dependant upon which letter is selected, the whole row is ‘colorized’. I can’t find any ‘code’ that is doing it… and I’m not seeing a macro.

I’m wanting to do something similar to a different doc…..
I can’t get the file zipped small enough to attach

HELP!

Reply | Quote

Replies

It sounds like conditional formatting. With the cells you want to format select Format>Conditional Formatting…

You can set formats based on a formula or value in a cell.

Reply | Quote

I was just honing in on that….
I clicked the row and conditional formatting. This is the “code” for the formula:
=UPPER(LEFT($C3,1))=”R”
I don’t know what “UPPER” stands for
I don’t know what “LEFT stands for…
I was in Cell C3… Why reference cell C1?
and for the “R”… When I typed “Retired into the cell, it formatted as expected.

Do I have to go through EVERY Row to set the “C” column?

-not quite as confused, Jody

Reply | Quote

UPPER – converts the value to upper case
LEFT($C3,1) – takes only the first character of the text in cell C3
=UPPER(LEFT($C3,1))=”R” This ensures that if “r” is entered rather than “R” the formatting still works. As in your example, if your text starts with an R it applies the formatting.

The conditional formatting formula pointed to one particular cell as it wanted the formatting applied across the whole row when that 1 cell was changed.

If you select the whole row before you apply your conditional formatting then it will be applied to every cell in the row automatically. If you want it to be based on the value in a particular cell then use absolute reference (eg $C$1)

Reply | Quote

UPPER – converts the value to upper case
LEFT($C3,1) – takes only the first character of the text in cell C3
=UPPER(LEFT($C3,1))=”R” This ensures that if “r” is entered rather than “R” the formatting still works. As in your example, if your text starts with an R it applies the formatting.

The conditional formatting formula pointed to one particular cell as it wanted the formatting applied across the whole row when that 1 cell was changed.

If you select the whole row before you apply your conditional formatting then it will be applied to every cell in the row automatically. If you want it to be based on the value in a particular cell then use absolute reference (eg $C$1)

Reply | Quote

=UPPER(LEFT($C3,1))=”R”
This is:
Look at the leftmost 1 character in cell C3. Make it uppercase and compare it to the letter R. If that statement is true add the format conditions you have set via the button in conditional formatting.

The $C3 means that the row number 3 is relative (note it is not “$3”) but that the column letter C is “locked”

When you select a range and set the formula, you base in on the “active cell” in the formula. This formula will always look at column C since it is locked (has the $ no matter what column is selected, but the row will change (no $). You have to highlight the entire range you want conditional formatting at the beginning, you do not need to do it row by row.

If this is not clear enough, please post back with additional quesitons.

Steve

Reply | Quote

We’re getting closer, We’re getting closer!!!!

OK, I highlighted row’s 5-16 (the rows that I want to apply conditional formatting to). Into the the ‘Formula Is’ text box, I entered =UPPER(LEFT($C,1))=”R” It doesn’t like my ‘code’… I don’t know what to put after the $c … I think that’s my problem anyway…
-Jody

Reply | Quote

OK…. Work around found…. I still don’t know how to “code” it, but if I select paintbrush from the one row that I Contitional Fromatted and paint the rest of the rows….
IT WORKS

PS…. I’d still like to know what range I was supose to have placed in the fromatting box.

Reply | Quote

OK…. Work around found…. I still don’t know how to “code” it, but if I select paintbrush from the one row that I Contitional Fromatted and paint the rest of the rows….
IT WORKS

PS…. I’d still like to know what range I was supose to have placed in the fromatting box.

Reply | Quote

Since you started in row 5 you should use $C5, the row of the active cell.

Steve

Reply | Quote

Since you started in row 5 you should use $C5, the row of the active cell.

Steve

Reply | Quote

$C is not a valid cell reference. You need to add the row number of the row you are typing the formula into. You said that you “highlighted row’s 5-16”. If the active cell is in row 5, then the formula should have been:

=UPPER(LEFT($C5,1))="R"

Reply | Quote

TY

Reply | Quote

TY

Reply | Quote

$C is not a valid cell reference. You need to add the row number of the row you are typing the formula into. You said that you “highlighted row’s 5-16”. If the active cell is in row 5, then the formula should have been:

=UPPER(LEFT($C5,1))="R"

Reply | Quote

We’re getting closer, We’re getting closer!!!!

OK, I highlighted row’s 5-16 (the rows that I want to apply conditional formatting to). Into the the ‘Formula Is’ text box, I entered =UPPER(LEFT($C,1))=”R” It doesn’t like my ‘code’… I don’t know what to put after the $c … I think that’s my problem anyway…
-Jody

Reply | Quote

=UPPER(LEFT($C3,1))=”R”
This is:
Look at the leftmost 1 character in cell C3. Make it uppercase and compare it to the letter R. If that statement is true add the format conditions you have set via the button in conditional formatting.

The $C3 means that the row number 3 is relative (note it is not “$3”) but that the column letter C is “locked”

When you select a range and set the formula, you base in on the “active cell” in the formula. This formula will always look at column C since it is locked (has the $ no matter what column is selected, but the row will change (no $). You have to highlight the entire range you want conditional formatting at the beginning, you do not need to do it row by row.

If this is not clear enough, please post back with additional quesitons.

Steve

Reply | Quote

I was just honing in on that….
I clicked the row and conditional formatting. This is the “code” for the formula:
=UPPER(LEFT($C3,1))=”R”
I don’t know what “UPPER” stands for
I don’t know what “LEFT stands for…
I was in Cell C3… Why reference cell C1?
and for the “R”… When I typed “Retired into the cell, it formatted as expected.

Do I have to go through EVERY Row to set the “C” column?

-not quite as confused, Jody

Reply | Quote