Conditional Formatting

Copper Contributor

Good Morning Excel Community,

 

I am in the process of creating a model with specific formatting to allow external users to follow and understand the file.

 

Can you help develop this "conditional formatting" to help me achieve my task instead of doing things manually?

 

  1. I would like to be able to select a selective cell range and if there is a "hardcoded" input then that cell range will be highlighted in the color of my choice -- light yellow
  2. For all standard text  such as descriptions, column headings, etc -- the text color  would be black, and for any other "important notes" the color will be blue
  3. for calculation 
    1. if there is a formulaic then highlight the cell medium gray 
    2. if there is a reference to an external reference,  highlight the cell medium gray with the font being bold color
    3. If there is an error within the workbook, I would like the font to be red.

can should one illustrate one formalize I can use within the conditional formatting command to make this happen in my model?   Want to make model more customized and less manual

 

 

3 Replies

@elo0707 

 

Your request itself brings forth a number of questions that need to be addressed.

 

Perhaps foremost, since you're developing this for "external users to follow and understand" would it be possible for you to post a copy of what you have developed so far? That would help your would-be helpers "to follow and understand." Post a copy, if it's possible, on OneDrive or GoogleDrive with a link pasted here that grants access.

 

That said--and please post if at all possible--the plethora of colors you are describing could actually make things more confusing, the exact opposite of your goal. A clear/clean design in terms of data entry points (Input) and separate area for results (Output) --in different parts of the workbook, quite possibly separate tabs--might help more than differentiating by color but having Input and Output distributed randomly throughout.

 

It also would help if you'd describe for us the bigger picture: what is this spreadsheet all about? Who are the users? What's the context?

Hello, Sorry for the late response. I have uploaded a test file to explain better what I want to accomplish. Thanks.

@elo0707 

 

OK. I've added to your comments and created one condition formatting rule. I've added comments to the other requests that explain why I don't think conditional formatting, per se, is what you need. You'd be making things far more complicated than is necessary, for the most part. Just format them according to what formulas are in the cells already. Or their placement.