Conditional Formatting Question

data808

Active Member
Joined
Dec 3, 2010
Messages
290
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
742
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Can you post your formula in Cell Q89?
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
6,736
Office Version
  1. 365
Platform
  1. MacOS
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

Active Member
Joined
Dec 3, 2010
Messages
290
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Oct 24, 2012
Messages
6,736
Office Version
  1. 365
Platform
  1. MacOS
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
CD
4150working units 150
5151working units 151
Sheet1
Cell Formulas
RangeFormula
C4C4=RIGHT(D4,3)
C5C5=RIGHT(D5,3)*1


then setup 2 rules
1 for green and 1 for red
 

Attachments

  • Screenshot 2022-08-25 at 10.19.15.png
    Screenshot 2022-08-25 at 10.19.15.png
    118.7 KB · Views: 1

data808

Active Member
Joined
Dec 3, 2010
Messages
290
Office Version
  1. 2019
Platform
  1. Windows
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
CD
4150working units 150
5151working units 151
Sheet1
Cell Formulas
RangeFormula
C4C4=RIGHT(D4,3)
C5C5=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

Well-known Member
Joined
Oct 24, 2012
Messages
6,736
Office Version
  1. 365
Platform
  1. MacOS
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
ABCD
1
2working units 123.567123.567
3working units 123123
4working units 11
5working units 1.5701.57
6working units 123.571123.571
Sheet6
Cell Formulas
RangeFormula
C2:C6C2=RIGHT(A2,LEN(A2)-FIND("/",SUBSTITUTE(A2," ","/",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))*1
 

data808

Active Member
Joined
Dec 3, 2010
Messages
290
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Oct 24, 2012
Messages
6,736
Office Version
  1. 365
Platform
  1. MacOS
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
 

Forum statistics

Threads
1,186,082
Messages
5,955,721
Members
438,213
Latest member
RobA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top