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.

 

[gnaga]

Can you post your formula in Cell Q89?

 

[etaf]

i suspect its changing the value to text , using concatenate
try *1 either in the cell or in conditional format formula
*1 changes from text to a value
or post the formula you are using

 

[data808]

Can you post your formula in Cell Q89?

Thanks. I will try and post it tomorrow as the file is at work.

 

[data808]

i suspect its changing the value to text , using concatenate
try *1 either in the cell or in conditional format formula
*1 changes from text to a value
or post the formula you are using

Sorry file is at work. I'll try and post it tomorrow.

Q89 does have text and numbers in it. The Q89 cell does a calculation but reflects a total that says "Working Units: 124" for example. It does break it down into sometimes decimals too like "Working Units: 89.71". So for the merged R5-7 cells I think I did the same concatenate function but without the word "Working Units:" and I just have a number to show in it but the conditional formatting doesn't seem to work properly while this concatenate function is going on. 150 working units is our quota and so I would like the merged R cell to turn green when they hit that 150 number or higher and also have cell be red when its 0-149.99.

 

[etaf]

it almost certainly will be text then
whatever the formula is just times by *1
then it should be a number - numbers are usually right justified in the cell and text left

see here

Book6
	C	D
4	150	working units 150
5	151	working units 151
Sheet1

Cell Formulas
Range	Formula
C4	C4	=RIGHT(D4,3)
C5	C5	=RIGHT(D5,3)*1

then setup 2 rules
1 for green and 1 for red

 

[data808]

it almost certainly will be text then
whatever the formula is just times by *1
then it should be a number - numbers are usually right justified in the cell and text left

see here

Book6
	C	D
4	150	working units 150
5	151	working units 151
Sheet1

Cell Formulas
Range	Formula
C4	C4	=RIGHT(D4,3)
C5	C5	=RIGHT(D5,3)*1

then setup 2 rules
1 for green and 1 for red

Cool thanks for the suggestion. What if the number is sometimes decimals too and not a whole number? Like Working Units: 151.25? Would the formula be =Right(D4,5)? Or do you have to count the decimal to so =Right(D4,6)? Also what would happen if it does turn out to be a whole number on some occasions? Will it start to pull some of the "Units" word into C4's cell?

 

[etaf]

right() is going to need to know the length of the numbers , and yes it will extract words etc , - i just used that as a simple example to show how extracting a number still leaves it as text

you said you have already extracted just the number anyway, have you done this OK - if not there will be a way to use right

edit

assumes the last space will be before the number part

=RIGHT(A2,LEN(A2)-FIND("/",SUBSTITUTE(A2," ","/",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))*1

Book3
	A	B	C	D
1
2	working units 123.567		123.567
3	working units 123		123
4	working units 1		1
5	working units 1.570		1.57
6	working units 123.571		123.571
Sheet6

Cell Formulas
Range	Formula
C2:C6	C2	=RIGHT(A2,LEN(A2)-FIND("/",SUBSTITUTE(A2," ","/",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))*1

 

[data808]

right() is going to need to know the length of the numbers , and yes it will extract words etc , - i just used that as a simple example to show how extracting a number still leaves it as text

you said you have already extracted just the number anyway, have you done this OK - if not there will be a way to use right

Thanks. Yeah I figured out a way to get rid of the "Working Units:" word so that it just shows the number in the merged R cells. I figured it would be easier that way. So only the Q89 cell shows the words with numbers in it but thats not the cell for the conditional formatting. I want it for the R cells which only shows the number. Its just weird that the conditional formatting doesn't work and it has got to be because of the concatenate function as I tested this conditional formatting on regular none formulated cells and it worked fine.

 

[etaf]

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