Conditional Formatting Assistance...

[dougcash35]

I am trying to assist someone with reconciling all of their business financial information into one excel workbook. It has become a little complicated now that I have worksheets for bank information, time keeping information, and multiple quick book exports. I have noticed that the check numbers in the bank information are not matching all the time with the check numbers that were entered into quick books.

Is there a formula I can use to make the check numbers on the bank information sheets turn green if they are on the quick books sheet, and turn red if they are not?

Bank Information Sheet
​

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Check No (Column A)
[/TD]
[TD](Column B)
[/TD]
[/TR]
[TR]
[TD](Row 1) 1
[/TD]
[TD]100.00
[/TD]
[/TR]
[TR]
[TD](Row 2) 2
[/TD]
[TD]200.00
[/TD]
[/TR]
</tbody>[/TABLE]

Checks from Quick Books
​

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Check No (Column A)
[/TD]
[TD](Column B)
[/TD]
[/TR]
[TR]
[TD](Row 1) 1
[/TD]
[TD]100.00
[/TD]
[/TR]
[TR]
[TD](Row 2) 3
[/TD]
[TD]300.00
[/TD]
[/TR]
</tbody>[/TABLE]

I would really love it if I could just see at a glance which checks are not accounted for!

Thanks!!

 

[Joe4]

Welcome to the Board!

Normally, Conditional Formatting doesn't like references to other sheets. But you can get around that by using Named Ranges.
So, highlight column A on your Quick Book sheets, and name it something like "qbooks".
Then you can highlight column A on your Bank Information Sheet, bring up Conditional Formatting, and use the following Conditional Formatting formula:

=and(row()>1,countif(qbooks,A1)>0)

and choose the green formatting option.

You can do the same for red, just set your COUNTIF=0 instead.

 

[dougcash35]

Thanks for the assist. It worked...kinda....

Some of the items turned green as ordered, but others didn't change color. I wish there was a way to show you! I have check numbers that definitely match on both sheets but are not changing color at all.

Thoughts?

 

[Joe4]

No need to send Private Messages. I see all replies made in threads I am participating in (under "Subscribed Threads").

So, your "red" formula should just look like:

=and(row()>1,countif(qbooks,A1)=0)

So, you should have two CF Conditions set up.

If it is not working, please explain your data structure and exact steps.
Are you highlighting all of column A when you enter the formula?
What row does your data start on?
Are you changing the formulas in any way?

Note that you cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[dougcash35]

I have two CF rules set. The first is for checking for check numbers that match to turn them green, the second is for checking for check numbers that dont match to turn them red. In my worksheet for the bank information, all check numbers are in column A, from rows 130 to 276. The second worksheet is the checks from Quick books. I highlighted all the check numbers in the quick books sheet (C3:C51) and named the information "QbookChecks".

Here are the formulas I am using:

=AND(ROW()>1, COUNTIF(QbookChecks, A1)>0) Green
=AND(ROW()>1, COUNTIF(QbookChecks, A1)=0) Red

1. Yes I highlighted the entire row of information on the bank information sheet prior and entered the formula on the entire set of values.
2. Cells A130:A276 on the bank page; C3:C51 on the Quick books page
3. Formulas only changed to reference what I named the cells in the Quick books page.

 

[Joe4]

1. Yes I highlighted the entire row of information on the bank information sheet prior and entered the formula on the entire set of values.
2. Cells A130:A276 on the bank page; C3:C51 on the Quick books page

I asked about selecting the entire column on the Bank Information Sheet, not the entire row.

If you are just highlighting rows 130:276, then you need to adjust the formulas accordingly.
The rule is this: when selecting a multi-cell range to apply Conditional Formatting to, write the formula as it applies to the first cell in your selected range. Excel will adjust for the other formulas.
If you are highlighting multiple cells across the row, you will also need to use absolute referencing to lock down the column reference.
If you are not including the title row in your highlighted range, you can scrap the ROW part of the formula.

So your formulas should probably look like:

[COLOR=#333333]=COUNTIF(QbookChecks, $A130)>0[/COLOR]
[COLOR=#333333]=COUNTIF(QbookChecks, $A130)=0 [/COLOR]

 

[dougcash35]

Well,

It colored all of the cells red or green, but there is no rhyme or reason why some are red and should be green or why some are green that should be red. I copy/pasted the formulas you suggested directly into the CF rule. something else must be wrong with my spreadsheet that is stopping it from working.

 

[Joe4]

It colored all of the cells red or green, but there is no rhyme or reason why some are red and should be green or why some are green that should be red. I copy/pasted the formulas you suggested directly into the CF rule. something else must be wrong with my spreadsheet that is stopping it from working.

Whenever that happens, it usually indicates that your Conditional Formatting formula does not correspond with the range you have selected to apply the Conditional Formatting to.

Do the following:
1. Remove ALL conditional formatting from your Bank Information Sheet
2. Select the range you want to apply the Conditional Formatting to
3. Enter your Conditional Formatting formulas and options

If that does not work, tell me EXACTLY what you did for steps 2 and 3 (what cells you selected, and the exact formulas you entered).

 

[dougcash35]

It's working! Thanks for the help! Side note, I just noticed that one of the check numbers that came up as matching had different amounts. Is there a way to highlight those cells if the check numbers match but amounts don't?

 

[Joe4]

Highlight both columns A and B on your quick book sheet, and give that a named range (I used "qblookup" for this example).
Then, your formula would look something like this:

=AND(COUNTIF(qbooks,$A130)>0,VLOOKUP($A130,qblookup,2,0)<>$B130)

Note, you will want this to be the first CF rule (you can re-arrange them) and be sure to check the "Stop If True" option.