MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Conditional Formatting

Posted by Glenn Balian on January 30, 2002 5:58 AM

If a calculated cell has a conditional format assigned is there any way to have a cell (linked to the calculated cell) change format when the calculated cell changes:

(e.g. Calculated cell B23 turns red because of conditional formatting. Now I want Cell A1 to shade red as well. Or, if B23's font turns red because of conditional formatting, I want Cell A1's font to turn red as well)


Posted by Mike on January 30, 2002 8:05 AM

Are you familiar with writing Macros and attaching them to Buttons?
If not let me know and I can walk you thru it.

There may be an easier way to do it by right clicking on the sheet tab , VIEW CODE, and enter the steps here, but I'm not familiar with that task.


Posted by Glenn Balian on January 30, 2002 11:14 AM

For most of my macros, I've used the "record button". I would be most appreciative if you were able to help me with getting this done. Unfortunately, the conditional formatting won't let me get done what I need.

Posted by Mike on January 30, 2002 11:47 AM

I've tried what u r attempting and it worked fine.

In my example I was going to enter data from B1:B20, and as I changed the cell formats (Red Font, or Background color), whatever I have in A1:A20 would change also.

I hope this is what you are trying to accomplish.

GOTO Tools: Record Macro; Name it what ever you
want or attached it to a CTRL Key.

The I selected F5 for my GOTO and entered B1.
Then I selected Range B1: B20,
Selected the Paintbrush and then clicked on A1.
Selected a1 again to bring me back to a starting point then Stopped Recording.

I attached it to a Button which I placed on the worksheet and assigned it the macro.

I entered data into both columns then used the button. Worked fine.

Hope this helps. I'm not as good as all these other guru's, so someone may tell me I'm nuts and suggest another method. From which I'll learn as well.

Let me know if this worked for you.


Posted by G Balian on January 31, 2002 6:21 AM

Great idea Mike,
Unfortunately it didn't work.
I stuck with having the number as well as the color in the summary cell.
For some reason, the macro copies the conditional format of the cell and requires the value in the cell in order to obtain the right color. Excel over-rides what the color of the calculated cell is and goes with the conditional format. The referred cell needs a value in order to turn the right color.
But, thanks for the idea! I'll talk to you soon.