Randy posted this question on the brilliant Excel-G discussion list:

Morning ALL, Happy Valentines to you and yours.

I am in need of help with a TRUE statement in the form of a formula for Conditional formatting in Excel 2007I have a check register that I want to use an X if the amount has cleared, an M for memo to the account, and a H for HOLD.

I have created a formula to create 1, 2, 3, In column A for the statement above.   so if X is in cell B3 then 1, if M then 2, if H then 3.and so on.

I have created the TRUE statement in the conditional formula with =AND($A:$A=”1″)   then the condition is TRUE changing all cells in the row ( B3:J3)  to Orange,  and so on,  I have approximatly 350 rows in my sheet.  and this formula doesn’t work for conditional format.

Does anyone have a better idea on this ?



Here is my response and there is a link to the sample file I created in case you need further help with working through the Excel 2007 Conditional formatting way of doing things:

Try this Randy,
Firstly, I agree with Wyatt that you don’t need to translate X into 1 … just leave the X/M/H column alone
Select the entire range you want to format: extending beyond that for future entries if you wish.
Now select Conditional formatting and select the New Rule option
Select Use a formula to determine which cells to format and enter three separate formulas:
=INDIRECT(“B”&ROW())=”X” for the cleared cheque option … format as you wish
=INDIRECT(“B”&ROW())=”M” for the memo cheque option … format as you wish
=INDIRECT(“B”&ROW())=”H” for the hold cheque option … format as you wish
… see my sample file for why there is “B” in this formula … see below
Click OK OK

The =INDIRECT() function is used to tie the row you are in to the value in column B where the value X/M/H is to be found

The question and full thread, including this reply can be found at: http://peach.ease.lsoft.com/archives/excel-g.html … it’s a free discussion list and it really is brilliant!

Click here to download the Excel 2007 file I created to demonstrate the solution here.

Duncan Williamson

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: