Conditional Formating based a value entered into the first cell of a row

[avidcat]

Is it possible to do a conditional formating based on a value being entered into the first cell of a row?

Basically when a date is entered into a cell in column A, I want the cells in that same row (A-J) to have a border, even if B-J do not have any value entered into them.

I appreicate an help,

thanks,
Nick

[teylyn]

avidcat, please update your profile to indicate your Excel version. There are differences between Excel pre and post 2007.

In XL 2003 , starting in row 1, select all the cells you want to apply the format to, then click Format - Contitional Formatting - Formula is and enter the formula

=$A1=<whatever your condition is>

Then select the format and click OK

You can use the format painter to apply the same format to other rows, or you can copy the cells and use paste special - Formats to copy the format to other rows.

Note the position of the $ sign. It will ensure only column A is checked, but it will adjust the row number to the current row.

hth

[avidcat]

Thanks, I didn't know there was an option for what version I am using in my profile. I updated it.

I am using Office 2007, what this be different?

thanks,
Nick

[teylyn]

In Excel 2007

starting in row 1, select all the cells you want to apply the format to, then click Contitional Formatting on the Home ribbon

click New rule and select "use a formula to determine ....

enter the formula

=$A1=<whatever your condition is>

Then select the format and click OK

use the copying techniques described above to apply the conditional format to other rows than the selected ones.

[avidcat]

Thanks,

Do you know what formula can be used if A1 for example equals anything?

[teylyn]

If you want to check if A1 is not blank, use the following:

=NOT(ISBLANK($A1))

edit: formula corrected to avoid confusion

this will be true (so the conditional format will be applied) if A1 is
- text
- number
- formula returning blank
- formula returning 0
- zero

[avidcat]

I attached a sample.

I believe I did everything correctly but it is not working.

Maybe I didn't enter something correctly.

thanks for the help.

Nick

[teylyn]

Sorry, my bad. The formula needs to be

=NOT(ISBLANK($A10))

applied to row 10

and of course, you have to set a format for when the condition is true, like a color fill for the cells, otherwise, you won't see the difference.

[avidcat]

Thanks teylyn,

That worked just as I needed it.

Nick

[teylyn]

Phew! I can call it a day, then!