Conditional Formatting cells (which contain formulas) based on another cell value.

[Luisftv]

I have TWO sets of ranges, each set contains six ranges, for a total of 12 ranges or clusters of data.

Each range is 11 columns by 70 rows.

The first set of ranges (six in total) have formulas which link to another sheet (but in the attached file I simply copied the values as dummy data). The data in this ranges goes like this: 8+19+4+17= per each cell.

The second set of ranges (six in total) contain cells with formulas where the previous values have been separated into their own cells like this: 8, 19, 4, 17 (in one cell there is 8, in another cell there is 19, and so on... the + and = sign have been removed).

Now, I'm trying to conditionally highlight the SECOND SET of ranges in two ways:

One. I need to highlight in "Range one" (P2:Z71) all the cells which contain a specific value based on another cell, Q74. For example, in cell Q74, I entered 17 and I need all the cells with the value 17 to be highlighted in range 1, P2:Z71 (see attached file)

Two. I need to highlight in all ranges the cells which contains a specific value based on several cells. For example, in cells Q76, S76, U76, W76, Y76, and AA76 I entered different values that I need highlighted throughout all six ranges (P2:Z71, AA2:AK71, AL2:AV71, AW2:BG71, BH2:BR71, and BS2:CC71).

NOTE: if a cell is blank, it should be left blank, not highlighted.

Conditional formatting works fine when the cells have direct data (text, numbers)... but since those cells have formulas in it, I cannot make it work... I don't know how...

I found another thread ( https://www.excelforum.com/excel-gen...ease-help.html ) but even the sample Excel file doesn't do what I need... although the title describes my problem.

Please help... and thank you so much in advanced.

[etaf]

the formula is retuning a TEXT value and you are comparing with a Number value

Add VALUE() to your formulas
=VALUE(SUBSTITUTE(TRIM(MID(SUBSTITUTE($B10,"+",REPT(" ",LEN($B10))),LEN($B10)*(COLUMN()-16)+1,LEN($B10))),"=",""))
or *1

then conditional formatting
like
=P2=$Q$74
will work

[Luisftv]

Thanks !!!!!!!!! That worked...

Aside from using conditional formatting to turn into white all the #### when the cell is blank (after adding the VALUE in the formulas), what can I add to the formula so that the cell is simply left blank?

Thank you so much.

[etaf]

its returning a #value error
so replace the formula with
=IFERROR(VALUE(SUBSTITUTE(TRIM(MID(SUBSTITUTE($B3,"+",REPT(" ",LEN($B3))),LEN($B3)*(COLUMN()-16)+1,LEN($B3))),"=","")),"")
and blanks will be entered , so you wont need to do anything in conditional formatting

[Luisftv]

Awesome!!! Thank you.

Cheers.

[etaf]

you are welcome
thanks for the rep