Concatenate cells and hold onto conditional formatting

AmberJo

New Member
Joined
Apr 15, 2002
Messages
17
This is a tricky one (or perhaps the flu has just spread to my brain). In cell C3 I'm combining some cells (with formula =A2&", " &A3& ", " &A4&", " &A5&", " &A6&", "). All works fine. However the cells have conditional formatting based on what is in column B. How can I hold onto this conditional fomatting (which makes the text pink if B3 = done). Can I do this? It would practically save my life if I could.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
AmberJo
I can't quite see what the problem is as you seem to have identified all that is required to make it work. I tried it and it responded Ok.
The condition used in the conditional formatting of cell C3 was:
[Formula is][ =$B$3="done" ]
Note the absolute reference)
Remove the relevant "$" signs to make the cell reference relative rather than absolute (or a mix of the two)if the conditional formatting is to be dependent on cells in column B other than B3.

HTH
 
Upvote 0
Sorry- wasn't entirely clear. I want the conditional formatting in column A to be transferred across to column C where the conatenate is done. For example, if I have 3 items to be transferred to column C and the first two were pink, then I want them to appear as pink in cell C2, with the last number in cell C2 as black (the normal font colour). Am I making any sense?
 
Upvote 0
I'm not entirely certain how the code will work for that, but what I do know is that you need to have Excel 2000 or better to have that ability.
 
Upvote 0
AmberJo
You were right about the task being tricky!! (But in addition, maybe you did have the flu when you first got the idea to set up your spreadsheet this way!)

Whilst one can do many fancy things with Excel, I believe in the KISS principle, otherwise you end up with such a complicated mess that no-one can understand, follow or debug the thing.

What you're trying to do (conditional formatting of separate parts of the value returned by a formula)just seems a bit extreme for my mind & I'd suggest that you have another think about what you're really trying to achieve, and how you might go about that using Excel. One thing you can bet on with Excel is that there is more than one way to skin the same cat.

Cheers
BigC
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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
Back
Top