conditional formatting question

[mdf24]

I am trying to develop an Excel based check register. When a check is written, the employee would enter the spreadsheet and input the check information on the exact line that corresponds with the correct check number. In the past we have used a paper log and I have often noticed that sometimes a check that has been written has not been entered onto the paper log...SO in developing the electronic log, I was hoping to make some type of a feature that if a line is skipped it will highlight the skipped row bright orange or something to ensure that it gets filled out.

Also, if a check is written over $3,000 an additional procedure must be followed. I would like to also highlight the cell, or make some type of warning if the amount of the check is >$3,000.

Thanks

 

[SteveO59L]

Try something like

"Formula Is"

=and(b1="",b2<>""

 

[NBVC]

For the second part,

Select the cell you want highlighted, then from Format|Conditional Formatting, select Cell Is and greater than and enter 3000.

Then select the format.

BTW, Steve059L's formula needs a closing paranthesis :wink:

 

[mdf24]

I can't get SteveO59L's function to work. When I enter that function it applies my formatting to the whole data set. Here is a sample copy of my sheet. So in the example below I would like the row for check # 20324 to be highlighted because it was skipped, however check numbers 20330 - 20335 have been issued yet so should not be highlighted. Any advice? Thanks

Check # Date Amount Payee
20310 11/22/05 $3,012.00 Bob Smith
20311 11/10/05 $3,000.00 Bob Smith
20312 11/11/05 $3,001.00 Bob Smith
20313 11/12/05 $3,002.00 Bob Smith
20314 11/13/05 $3,003.00 Bob Smith
20315 11/14/05 $3,004.00 Bob Smith
20316 11/15/05 $3,005.00 Bob Smith
20317 11/16/05 $3,006.00 Bob Smith
20318 11/17/05 $3,007.00 Bob Smith
20319 11/18/05 $3,008.00 Bob Smith
20320 11/19/05 $3,009.00 Bob Smith
20321 11/20/05 $3,010.00 Bob Smith
20322 11/21/05 $3,011.00 Bob Smith
20323 11/22/05 $3,012.00 Bob Smith
20324
20325 11/22/05 $3,012.00 Bob Smith
20326 11/23/05 $3,013.00 Bob Smith
20327 11/24/05 $3,014.00 Bob Smith
20328 11/25/05 $3,015.00 Bob Smith
20329 11/26/05 $3,016.00 Bob Smith
20330
20331
20332
20333
20334
20335

 

[NBVC]

You can revise to Steve's function to check if next row is blank,

=AND(B1="",A1<>"",B2<>""), this just check one blank row between to checks. Will this do?

 

[mdf24]

that works if I only apply that conditional format to the row missing the information, but if I apply the conditional formatting to the entire check register sheet, then it applies the formatting to the entire register. What am I doing wrong? Thanks

 

[NBVC]

Try freezing the column references

=AND($B1="",$A1<>"",$B2<>""), B1 is the first column after check number, A1 is the check number column. B2 is the next row.