Auto Shade Multiple Cells Based on Data in One Cell

Copper Contributor

I am creating a checklist and need to have multiple cells shaded based on the text in one cell.

 

Each time 'Sale' is typed into any row in column 'F', the same columns are shaded for that row.

 

I've attached my spreadsheet, which shows which cells should be highlighted for a 'purchase' 'sale' and 'mortgage' Basically I'm trying to create a checklist where I only have to type in the file type, and the irrelevent boxes for that file type are shaded in.

 

I tried to do rule, which worked well, but only applied the shading to the first cell.

 

Any help would be greatly appreciated, I've been working on this all day and can't figure it out!

15 Replies

@RozBloggs , something like this? 

 

ps: I've used the Filter function that is new to the Excel 365 version. Let me know if you are using an earlier version of Excel.

@TheAntony No I have the most current version!

@RozBloggs , great! Let me know it this is what you are looking for then.

@TheAntony on the right track! But when I type in Sale now, all the shading disappears, and then if I delete/change them it all disappears again... :(

@RozBloggs , Here's how I set it up to work (see attached video). Is this not what you were looking for? Can you share a screenshot or video of what it looks on your computer?

@TheAntony  Your video works perfect - that's exactly what I'm looking for!  I'm not sure why it doesn't work on the downloaded one?

 

When I open it it looks great:

RozBloggs_2-1596335883153.png

 

If I delete one item in the column it deletes all the shading:

RozBloggs_3-1596335922769.png

 

And when I add a new item to the column it doesn't add the shading:

 

RozBloggs_1-1596335838573.png

 

@RozBloggs It looks like you don't have the latest version of Excel that supports the new Dynamic Array functions. I'll see what I can do with traditional formulas. 

 

You can check if you have any updates by going to File->Accounts->Update Options->Update Now.

 

In the meantime, can you let me know if the functionality in the video is what you are looking for?

@TheAntony apologies, I thought it was as I bought the latest version under six months ago. I'm updating now - but yes, what shows in your video is exactly what I am looking for!

@RozBloggs , No worries. Here's a version that should work without the latest update. Also, I deleted the empty table rows since they were just taking up space. The nice thing about Excel Tables is that they will expand as you type in new info.

@TheAntony it works perfectly!  Thank you SO much, I'm so grateful that you took the time to do this!  Is there any way you could give me a quick explanation of what you've done/link to a video/tutorial so if I have to make changes I know what I'm doing?  I may have to add more columns in future.

@TheAntony sorry another thing - I need to be able to fill in the unshaded boxes (Y, N, R, or N/A) - but when I do that it gives me an error?

@RozBloggs , Glad it worked. Here's how I set it up. 

  1. Created a table (named it Checklist) to the side that contains File Type, Item and a simple combination of the two I called Key to be used in the lookup later. You can add items and filetypes here and it will automatically get picked up in the formula.
  2. Added this formula in the G2. It returns a TRUE if the combination of File Type and Item exists in the Checklist table:

 

=NOT(ISNA(MATCH([@[File Type]]&G$1,Checklist[Key],0)))​

 

  • Added conditional formatting that just pointed to the cell since I already have the TRUE/FALSE that CF needs:
    TheAntony_0-1596339191811.png

     

  • Changed the number format to ;;; so that the TRUE/FALSE don't show up in the cells:
    TheAntony_1-1596339260717.png

Hope this helps.

@RozBloggs , Didn't know that you wanted to use those cells for typing. I moved the formula into conditional formatting dialog. You should be able to use those cells now.

@TheAntony Amazing, works perfectly.  I can't thank you enough!

@RozBloggs , Happy to help. Enjoy!