Conditional Formatting Over a Large Range Not Working Properly

Ok...this is my first post on this forum. I tried looking around here and elsewhere for an answer to this but to no avail. I'm hoping someone here can help me!

I'm trying to apply conditional formatting over a large range of data, based on the parameters below.

Formatting is applied to columns O through IT. Affected rows are 16 and 17.

The data in row 16 is a sum of the data in rows 13-15. The data in rows 13-15 is derived from Vlookup formulas.

Row 17 is derived from a Vlookup formula.

I am formatting based on three groups. If the data in row 19 is within one of three ranges relative to row 18 it returns different results. I'm using these formatting formulas, in this order, with the "Stop if True" button checked:


=O17&"">=(0.95*O16&"")

=O17&"">=(0.85*O16&"")

=O17&"">=(0.75*O16&"")

The range of the formatting is =$O$17:$IT$17

Ok...with that said...my problem is as follows:

The formatting works exactly as it should from O17 up through FG17. Once I reach column FH it no longer works properly. At that point I'm automatically returning the formatting for the second condition even though none of the data fits in any of the formula groups. For instance, FH17 = 3 and FH 16 = 35.

I've ensured that the data formatting of Column O through Column IT is exactly the same (via format painter) so that shouldn't be the problem.

I'm pulling my hair out...any ideas?

Thanks in advance!

Ok I'll put something together and upload tomorrow.

Yes, I wish that would have worked as well. That was how I had written the formulas initially, but it didn't work. A popup showed up next to the cell that indicated that the data was numbers formatted as text. I selected the option to format as a number, but that didn't fix it either. It was only when I added the &"" that the formatting took hold.

The unfortunate problem I have with this spreadsheet is that I inherited it from someone else who built it. It does some weird things, such as when I insert a row or column a save box pops up each time...I've not seen that before.

I'm wondering if these issues stem from how the spreadsheet was originally built. I'm testing that theory by rebuilding the spreadsheet from scratch in another workbook.

Awesome! That worked like a charm! Thanks for your help. I can get my sanity back...

Any thoughts about the other thing regarding the save box popping up when I add columns/rows?

That is just more of a nuisance than anything else.