JARHTMD

Board Regular
Joined
Nov 16, 2009
Messages
57
I have an Excel2007 spreadsheet into which data is entered daily (1 row each day). Various comps (min, max, avg, etc) are shown in each row. Cols into which data is (or will be) entered are shaded light green. Cells for computed data are shaded pink and protected. Some cells have conditional formatting, usually removing shading for min and max values for the column.

Today, when data was entered, several green cells changed to pink (a darker shade than the computed/protected cells) and the font was changed to bold red. The darker shading makes me think that conditional formatting (not my strongest excel talent) is involved. I tried copying another cell in same column (both with & without data) into the problem cells. That fixed the problem for a couple of cells, but not for others. Removing data from a cell seems to change to the correct shading, but of course the cells need to be populated.

Is there a way to see "everything" about a given cell (formatting, conditional formatting, formulas, etc)? Hopefully, that way I compare the cells' properties (everything) and find the differences.
 
Last edited:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

BrianJN1

Well-known Member
Joined
Jun 15, 2017
Messages
603
Go to Conditional Formatting on the Home tab. At the bottom of the list select "Manage rules".
If you highlight any cell, or group of cells, you will see any rules that have been applied. Note in that drop-menu that there are options where you can delete those rules from a section of the sheet or even the entire sheet.
 

JARHTMD

Board Regular
Joined
Nov 16, 2009
Messages
57
My conditional formatting rules are all screwed up. Actually, from the looks of some, I'm surprised it has worked this long w/o problems. For most of the cells with CF (not just those that caused my problem) there are multiple "top 1" and "bottom 1" conditions. Some have 2 sets of top/bottom conditions. Some even more. All(?) seem to have similar/duplicate conditions, which probably accounts for the erratic nature of my problem. When a "conditional" cell was copied into a cell with existing conditionals does it replace the existing conditions? It seems that the new and old conditions are both kept (append; not replace).

I assume the easiest thing for me to do is to only fix the remaining rows of this sheet w/o trying to fix already populated rows. How do I remove all conditional formatting for those columns for the remaining days of the year (today thru bottom of sheet)? Then I suppose I can fix "today's" conditions and fill down thru the end of sheet.
 

BrianJN1

Well-known Member
Joined
Jun 15, 2017
Messages
603
When you are in Manage Rules look for the cells, or ranges that apply where you consider that there are errors, eg $Z20:$Z40 and edit that out.
Although you may delete that range from one rule it might show up in another, like: $A20:$A40,$Z20:$Z40. Those formulae will probably hold the sheet name there as well.
 

JARHTMD

Board Regular
Joined
Nov 16, 2009
Messages
57
Thanks BrianNJ1. It took me a while to figure out that I had to use the Format Painter and even longer to figure out that I had to remove all conditional formatting in the destination cells, else the source conditional formatting would be appended to any existing conditional formatting. It's not like copy/paste of data which overwrites existing.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,923
Messages
5,639,021
Members
417,062
Latest member
CM214

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