CODEWITHSUNDEEP
excel
Wonjae Jang
Wonjae Jang

Reputation: 51

How to fix conditional formatting not working on cells properly?

I am trying to set up some conditional formatting on an assignment and it just isn't working properly.

I have current a bunch of cells that need conditional formatting. If it is greater than 45, highlight red. If it is less than or equal to 45, highlight green. All these cells are referenced from other sheets.

Say I have 2 sheets. A cell in sheet1 (called cycles) has the following formula: 

=RIGHTCENTER!F10

Simple, just referencing a cell from another sheet. The cell that it references in sheet2 (called RIGHTCENTER) has the following formula:

=SUBSTITUTE(HLOOKUP(G3,Table25891314151834051[#All],A10,FALSE),"*","")

Kind of a mess, but pretty much what it is doing is looking through a table row and finding the most recent value that was inputed, and it is avoiding any cells in that row that have an *.

Now, in sheet1, I am trying to conditionally format that cell and many other cells that are pulling the exact same formula (with different numbers though). As I said above, if the number is greater than 45, red, less than or equal to 45, green. When I put this conditional format however, it gives me all the cells as red, even though they are less than 45. The only cells that I get highlighted green are the ones that are empty and don't have anything in them.

So I've tried asking a professional on Excelchat and they told me the only answer they had for me was to conditionally format each cell with the cell location in a formula. For example:

Formula: =O27>45 -> Format red
Formula: =O27<=45 -> Format green

O27 is the location of the cell (and then I just typed out what that formula does with an arrow and the color for clarity)

I have like 300 cells that need this and I can't see myself doing this for each and every cell. So they I thought I could possibly just self reference the cell using RC or ...

=CELL("width", INDIRECT(ADDRESS(ROW(), COLUMN())))

But that didn't work either. I tried the solutions in the following article to no avail. Excel Reference To Current Cell

Anyone got a solution that doesn't have me manually typing in the conditional format for every cell? Thank you so much in advanced and sorry about the wall of text.

Upvotes: 1

Views: 12678

Answers (1)

BigBen
BigBen

Reputation: 49998

Your SUBSTITUTE function is converting any numbers you had to text, which is causing the unexpected results in your conditional formatting.

To convert back to a number, you can use the double unary: --.

Depending on your data, that might be at SUBSTITUTE step: 

=--SUBSTITUTE(HLOOKUP(G3,Table25891314151834051[#All],A10,FALSE),"*","")

Or on the cycles sheet:

=--RIGHTCENTER!F10

Or adjust your formula to not use SUBSTITUTE.

Upvotes: 1

Related Questions