Using INDIRECT and AND/IF for conditional formatting

I am having some issues with using INDIRECT to apply conditional formatting.

I have column G which carries a percentage. I want to conditionally format column H based on the percentage being greater than 0 and less than 100.

I have attempted the following without success:

=AND(INDIRECT("G"&ROW())<100%,INDIRECT("G"&ROW())>0%)

This will return the correct value in a cell (true) and fail with conditional formatting

=IF(INDIRECT("G"&ROW())<100%,IF(INDIRECT("G"&ROW()>0%,TRUE))

This will return the correct value in a cell (true) and fail with conditional formatting

=IF($G2<100%,IF($G2>0%,TRUE))

I saw this in another solution. The $ is supposed to force a reevaluation on each row. This did not work either.

Additionally, I've tried both 100% and 1 in the formulas without success.

Any suggestions?

Solution 1:

You don't need the If in Conditional Formatting. Just use:

AND($G2<100%,$G2>0%)

It took me a while to stop wanting to include If in my Conditional Formats, but the CF itself is testing for the condition you specify, so no If needed.

Also the "$" is for a fixed reference. It forces the formula to always refer to column G, which would be relevant if your CF was for multiple columns. The lack of a "$" in front of the 2 makes that part of the cell address relative, which means the CF will always check the row it's in. Relative referencing is important to understand, so if my explanation doesn't make sense, you might want to google it.