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 ?*

*TIA*

*Randy*

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

### Like this:

Like Loading...