Problem with conditional formatting

mthornburg

New Member
Joined
Jun 11, 2008
Messages
8
Hi. I have conditional formatting set up so that when the value of a certain cell is TRUE, a range of cells elsewhere on the sheet grays out. It works, except for the fact that when it grays out, it only changes the first cell in the range and leaves the others the same color.

I read that you can use "Paste Special" and paste the formatting of that particular cell to others. I tried that. However, the problem is that that particular cell is in large font. Thus, when the format is transferred to other cells, they all become large as well.

Is there a way around this/a more efficient way to change a range of cells conditionally? I wish there were section breaks in Excel...
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Do you mean select the range I want to format before I click on conditional formatting? I've tried that and it only alters the active cell.

Or is there a way to extend the formatting of an already formatted cell to others?
 
Upvote 0
You set the formula to query the activecell againats an absolute cell, like

=A1=$H$1

CF adjusts the A1 per call.
 
Upvote 0
I'm extremely sorry, but I'm still not entirely sure what you mean. Is $H$1 just a random cell name?

Suppose that I have a range of cells: A2:G150 that I would like to gray out if A1's value is TRUE. Which cell name would I use in this case and where precisely would I put it so that instead of graying out only the Activecell, A2, it grays out the whole range?

Thank you so much in advance!
 
Upvote 0
I think what xld is saying is, in your updated example:

1. Go Format > Conditional Formatting.

2. Click on Cell A2 and the then resize to select range A2:G150, but note that A2 is the active (white) cell.

3. Assuming that cell A1 can take only a TRUE/ FALSE value, and that you want the CF to be applied to A2:G150 if A1 = TRUE: then in the Conditional Formatting options dialog, Click on the "Cell Formula is" drop down and type in the Formula Bar "=$A$1".

4. Make sure you change your CF pattern to grey or or anything else and click OK to Exit the CF dialog.

If A1 has the Boolean value TRUE then A2:G150 should have the CF applied to it.

Hope this helps.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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