COUNTIF Conditional Formatting Reversal and leaving rows blank

[b_pickett]

I am working on a spreadsheet, and I need to highlight blank cells prior to the text mark "AI." However, When I do this, it highlights the rest of the rows that are in that grouping where "AI" does not appear, and data has yet to be entered. I found this thread that got me part of the way there, but I need the rest of the rows to not highlight when there is no data entered.

I am currently using COUNTIF($F1:F1,"AI")=0

What modifications could I make to it in order to achieve this?

 

[Joe4]

Welcome to the Board!

I am having a hard time visualizing your question. It probably would make a lot more sense if you could post some sample data and expected output, so we can see exactly what you are working with and why you want the end result to look like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[b_pickett]

Welcome to the Board!

I am having a hard time visualizing your question. It probably would make a lot more sense if you could post some sample data and expected output, so we can see exactly what you are working with and why you want the end result to look like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I couldn't ger xl2BB to work, so I attached images The first one is what it currently looks like with the formula, the second is what i would like the formula to do

 

[Joe4]

Is it possible to have "ai" in a row, and have a mixture of blanks and "x" in the columns before the "ai" in that row?
If so, then do you only want the blank cells formatted red?

 

[b_pickett]

Is it possible to have "ai" in a row, and have a mixture of blanks and "x" in the columns before the "ai" in that row?
If so, then do you only want the blank cells formatted red?

Due to the nature of the spreadsheet, there will really only be blanks before it

 

[Joe4]

OK, so assuming that you are wanting to format rows 3:20, and want to go out as far as column AW, do the following:

1. Select the range A3:AW20
2. Go to Conditional Formatting
3. Select the "New Rule" option
4. Select the "Use a formula to determine which cells to format" option
5. Enter this formula:

=AND(A3="",COUNTIF(B3:$AW3,"ai")>0)

6. Choose your Red formatting option
It then should do what you want, i.e.

 

[b_pickett]

That worked! Thank you so much!

 

[Joe4]

You are welcome.
Glad I was able to help!