"Locking" a Cell Fill Color

I have tried to figure this out but to no avail. How do you "lock in" a cell color so that if you want to change cell colors later, that color won't change. In other words, one cell color has a priority over another one (e.g. yellow over grey or "no fill").

Here's an example: I want specific cells in column H to always be yellow. Let's say the range is H5:H800. But I have a need to change row colors based on other criteria, so I want to be able to alternate between grey and white for example with the row colors. So let's say I want to change rows 60-70, 110-145, and 155-157 to be all grey in all columns EXCEPT the column H that I want to stay yellow no matter what.

Also, I looked into conditional formatting but the thing is that my decision to color the cells is not based on a specific value or a formula. For instance, let's say I have hundreds of invoices with many line items in each invoice and that I want to list the line items row by row in Excel. I'd like to alternate the color "white-grey-white" in the rows so that it's easy for me to see when one invoice data ends and the other begins. That is easy to do manually as I go along and add more data to my sheet except that when I change the row colors based on what "invoice data" it is, the column H with the color yellow gets changed too (and I'd like to keep that column yellow because it indicates cells where I have to plug in data). Hopefully somebody can follow what I'm saying.

Hi,

You could use a conditional format with the formula =COLUMN()=7 and yellow fill, with 'Stop if True' and make sure it's the first rule to be evaluated. See the attached sheet for this working.
CF to _ignore_ a column for other CF.xlsx

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

Thank you so much for the quick replies, everyone! This forum and the people here are truly remarkable!

I must say that I haven't used conditional formatting before so I'm having trouble figuring this out (how to create a new rule the way Aardigspook did), but it must be simple so I'll try to figure this out.

I have put in the formula =COLUMN()=7 and yellow fill, with 'Stop if True' (and it's the first formula to be evaluated), but I'm not sure what's going on with the "cell value =4, cell value =2" stuff. Sorry, I'm a complete beginner when it comes to conditional formatting.

Hi,

Sorry, I should have explained better - the other conditional formatting in the file I provided was just there to show how the =Column()=7 formula took priority over the other conditions. To apply this to your own file, apply any other conditional formats you want (including the alternate rows - which you can do automatically - see below). Then go to Conditional Formatting, click 'New Rule' and choose 'Use a formula to determine which cells to format'. Type =COLUMN()=7 into the formula box and select the format you want using the 'Format' button, then click 'OK'

Now click Conditional Formatting again and this time choose 'Manage Rules'. Select the rule you just made and put $G1:$G100 into the 'Applies to' box (make the range as big as you need it to be), then ensure that 'Stop If True' is selected. If you can't see the rule, you've probably clicked a cell outside the range it applies to - select 'This Worksheet' in the drop-down list at the top.

Highlighting alternate rows automatically:
Conditional Formatting → New Rule → Use a Formula to determine which cells to format. Put this formula in =MOD(ROW(),2) and select the shading you want for the alternate rows.


Hope that helps and sorry for the earlier confusion.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

Thank you! I kind of realized you were just showing how the priority works, but then I was a fool and didn't select the correct range for the rule. Now it works perfectly and I've learned a little bit about conditional formatting in the process! Thanks again!

You're welcome - glad I could help.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

Re: "Locking" a Cell Fill Color

lock cell colour but copy paste then do not change background color and font color