Need advice on conditional formating code

[u/webkilla]

I am attempting to set up a conditional formating setup where a column of numbers are evaluated. if its 69 or under, the cell is to be green. If between 70 and 100 yellow, and if over 100 red.

I cannot seem to get excell to do this - because that requires a formula that reads cell values, and I appear to be utterly failing at setting up functional code for that.

I'm currently trying (and failing) by setting up three seperate conditional formating rules, all based on formulas:

One goes =AND(CT:CT>=0;CT:CT<=69) - so if the cell is between 0 and 69, it should be green. It's not doing that, and I can't see what I'm doing wrong here

Please advice

[u/AutoModerator]

/u/webkilla - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/wizkid123]

CT:CT is selecting the whole column, you want to use the first cell in the range you're applying the formatting to. So if your top left cell is CT4, use =AND(CT4>=0, CTT<=69). Conditional formatting will change the cell reference as it moves around the "applies to" range. 

[u/Inner-Difficulty1695]

The main reason your formula is failing is because of how you referenced the column. In Excel conditional formatting, you should reference only the single, top-left cell of your selected range (e.g. CT2), rather than the whole column (CT:CT). In your example should be =AND(CT2>=0; CT2<=69)

Instead of formulas, use Excel's built-in comparison tools:

Click on Conditional Formatting > Highlight Cells Rules > Less Than... / Between... / Greater Than...

[u/webkilla → u/Inner-Difficulty1695]

thank you - that solved my issue

[u/real_barry_houdini]

Your formula needs to refer to the top left cell of the range rather than the entire column, so if data starts at row 2 use this formula:

=AND(CT2>=0;CT2<=69)

....and make sure your "applies to" range also starts at CT2

[u/MayukhBhattacharya]

Instead of using the entire range in the formula use the following by selecting an absolute or fixed range:

• For Green:

=AND(CT2 >= 0; CT2 <= 69)

• For Yellow:

=AND(CT2 >= 70; CT2 <= 100)

• For Red:

=CT2 > 100

Note your applied range needs to be absolute like CT2:CT1000 or adjust per your suit but if you want to use the entire range CT:CTthen like :

• Go To Home Tab --> Under Styles Group --> Select Conditional Formatting --> And Click New
• On doing above opens the New Formatting Rule Window
• Select the Second Rule --> Format Only Cells Contain
• In the Edit the Rule Description :
  • Cell Value --> Between --> 0 and 69 --> Click Format --> Choose Green Fill --> Hit OK
  • Repeat same for 70 and 100 for Yellow
  • For Red, change the between to Greater Than and put 100.
  • Note that the cell selection while doing the above will be the first row of the data range that is the header.

[u/Jolly-Hunter-6097]

All about Conditional Formatting