Conditional Formatting Question

[data808]

I have a cell R5, R6, and R7 merged as one. This merged cell is mirroring (= Q89) another cell (Q89) which has a grand total value in it and but uses a concatenate function to help sum up the total. I think the concatenate function may be causing the conditional formatting not work. I would like the R merged cell to highlight red if the value is less than 150 and turn green if its 150 or more.

Can someone tell me why this is not working? If I try it on regular cells without the concatenate function then it works fine. If VBA is better than I am open to that as well. Thank you very much for the help.

 

[data808]

it doesn't work because its not a real number , its actually text
as mentioned before, in previous posts - you need to change the text to a number and to do that you multiply by 1

formula() * 1
and then it will work

I thought i showed that in my previous posts and the image , sorry i'm obviously not explaining well

Thanks for the reply. I will try it out and let you know but just to show you what the concatenate function that was in these cells, here it is:

Q89 cell =CONCATENATE("Work Units: ",K99)

R6 and R7 merged cell =CONCATENATE(K99)

Let me know if this changes your suggestion for a solution to the problem. Thanks.

 

[etaf]

why not just use

=K99 in R6 / R7

not adding anything by using concatenate(K99)

is just the cell

merged cells R6 & R7
the reference would be R6 for conditional formatting

Book9
	A	B	C	D	E	F	G	H
1	123		123					Work Units: 123
2	12		12
3	12		12
4	60		60
5	12		12
6	70		70
7	12		12
8	12		12
9
Sheet1

Cell Formulas
Range	Formula
H1	H1	=CONCATENATE("Work Units: ",A1)
C1:C8	C1	=CONCATENATE(A1)*1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:D8	Expression	=C1>50	text	NO

 

[data808]

why not just use

=K99 in R6 / R7

not adding anything by using concatenate(K99)

is just the cell

merged cells R6 & R7
the reference would be R6 for conditional formatting

Book9
	A	B	C	D	E	F	G	H
1	123		123					Work Units: 123
2	12		12
3	12		12
4	60		60
5	12		12
6	70		70
7	12		12
8	12		12
9
Sheet1

Cell Formulas
Range	Formula
H1	H1	=CONCATENATE("Work Units: ",A1)
C1:C8	C1	=CONCATENATE(A1)*1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:D8	Expression	=C1>50	text	NO

Thank you so much for the help. It worked!! It's crazy how something so simple as "*1" can fix this issue. Thanks again.

 

[etaf]

you are welcome

Often see questions here where data has been imported or using text functions like concatenate() , right() , left() where the number looks ok , but is not a number
Often the justification gives it away - when its to the left of the cell its Text when to right a number