Expert: Adelaide carvalho - 7/7/2008

Question
Hi, this is an Excel conditional format related question. I have constructed a checkbook register of sorts; one of the graphic display features I wanted was to alternate the cell shading back and forth from row to row; white one row, the next row light blue, and so on. This was easily accomplished by applying the following formula under Conditional formatting: =MOD(ROW(),2)=1 I selected my applicable range, applied this conditional format, and all rows now alternate color from row to row. My Excel workbook contains several sheets. The conditional formatting I just described was applied to the "Main sheet." Now, on the Main Sheet, the content of column D can be determined by the content in column C. Column C is "Transactions,", Column D is "Memo". I have created a list (in columns A and B) on a separate sheet, entitled "Transactions," and have assigned roll-down menus for each cell in Column C on the Main Sheet. For each cell of Column D on the main sheet, I entered a VLOOKUP function: =VLOOKUP(C:C,Transactions!A$1:B403,2,FALSE) This allows me to do the following: when I enter a new entry (say a payment to a frequent vendor), I can select the vendor from the roll down list in Column C (if that vendor appears in my list on the "Transactions" sheet), and in the adjacent cell in Column D, under "Memo," the purpose is then entered automatically. This is a great time saver. I copied the VLOOKUP function to each cell in Column D. The trouble is that, without entries in Column C (which will appear only with the creation of new entries), each cell in Column D now yields the error message, #N/A. This could be remedied easily, if the entire sheet were white: I would use conditional formatting to set the font color of the error messages to white, using this formula: =ISERROR(B80:B65010) The trouble is, that every other row is shaded in light blue, and each cell has the format =MOD(ROW(),2)=1 already applied to it. What I want, in essence, is for each WHITE row to have the error message font set to white, and for each BLUE row to have the error message set to BLUE. This far exceeds my skill set... I'll be most curious to discover how this might be accomplished! Thank you, and very best wishes, Juergen

Answer
Hi Juergen

How are you?
What about joining the 2 conditions into one

=And(Iserror(B:B)=True, Mod(Row(),2)=1 )

and Format the cells on this composed condition?

When there is no error just use the condition

=And(Iserror(B:B)=false, Mod(Row(),2)=0 )


I guess that this will do the trick.

Cheers
Adelaide