Conditional Formatting

[wlambeth]

I am hoping someone out there can "school" me on the use of conditional formatting!

I have expense reports set up with data validation lists that allow the user to populate a three part GL account based on location, accounting code and department.
I then compare the users selection against the current chart of accounts (COA) and if there is a match it returns the GL account from the COA table.

I am trying to have the conditional formatting shade, the user selected GL account, red if the GL account does not currently exist in the COA.

So in cell F39 if there is a match the cell will show the GL account from the COA, if not the formula returns #N/A.
If cell F39 contains #N/A or if cell F39 does not match cell F40 (user selected GL account) then shade cell F39 red.

Please help as I can never grasp conditional formatting!

Thank you

 

[JackDanIce]

Formula you could try for CF is:

=NOT(ISNUMBER(MATCH(F39,[COA table],0)))

Where [COA table] should be replaced with the range the values you wish to match against exist.

CF formula rely on Boolean (TRUE/FALSE) outcomes, where TRUE triggers the CF formatting. In your case, you want to CF a cell if it doesn't exist, so your formula should test for a No Match. MATCH returns an integer and an error if no match. So we can test this as being numerical or not. If it's not a numerical answer, then it doesn't exist, which results in TRUE and the CF is triggered.