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
