Conditionally Format Based on Cell Fill

[rjbinney]

I have a grid that has some blank cells, some cells have data.

Blank cells are always filled BLACK.

Data cells are NOFILL until I complete a (separate, non-Excel, manual) action. Then I fill it with gray.

I have conditional formatting that wraps this week's row in YELLOW - just as a visual aid. (=WEEKNUM($A2)=WEEKNUM(NOW()))

I would love to fill this week's row in bright yellow as well - unless I fill it in BLACK or GRAY.

I can do the black with conditional formatting ISBLANK. It's the GRAY that's tough.

I was thinking I could have condition that said if the format of the cell is NO FILL AND it's this week, fill it yellow. That way, if I manually choose another color, it would ignore the CF.

I was originally experimenting with VB that said "If I add a color, stop conditional formatting" but couldn't get that to work.

 

[Joe4]

Conditional Formatting can only run on values that are returned in a cell, they cannot run off of manual formatting done to the cell.
You might be able to replace this Conditional Formatting with VBA code instead. You can have automated VBA code that runs upon the manual updating of the value of certain cells.
If that sounds like it would work, please provide to us the following information:
- What range of cells having their values manually updated should trigger this to run
- What range of cells should have the formatting applied to them
- What the conditions are, and what formatting you desire

You already briefly mentioned some of these things, but not it much detail.

 

[rjbinney]

Thanks. This is actually a silly little sheet, and it became more of a "Can I/How Do I" riddle.

The value of the data will be irrelevant to the formatting. The data indicates an assignment (so it's text and numbers). So the various alternatives are:

1. There was nothing to be assigned for that column that week
   (Empty cell, black fill)
2. There is no information yet about an assignment for that column that week
   (Empty cell, no fill - almost certainly only for current and future weeks, but there is the rare stray where we don't receive information until after the week has passed)
3. An assignment has been made for that column that week
   (Data in cell, no fill)
4. An assignment has been completed
   (Data in cell, grey fill)

And then I put in the CF to "highlight" the current week. Right now it's just a yellow border, but since you can't change line weights in CF, it's tough to see. So I was hoping that I could somehow fill the cells yellow that met conditions 2 and 3, but let my formatting cells manually in conditions 1 and 4 override.

I know I could change condition 1 to have some marker data (like "0") and condition 3 to include something like "TBD" in it, but that's more work than the "cure".

Thanks

 

[Joe4]

OK, but we still need to determine how this VBA code is to be triggered. The most common options are:
- It is run automatically upon a cell with that cell is manually update (however, this only applies to value updates, not formatting updates)
- It is run manually by the user on existing data whenever they choose
- It is run automatically upon some other event happening (there are other events like Sheet selection, Cell selection, Sheet re-calculation, Workbook opening, etc).

People often use the Event Procedure code (Worksheet_Change) described in the first option above to replace Conditional Formatting, though it only works as values are manually updated.
Read here for more on Event Procedures: http://www.cpearson.com/excel/events.aspx

 

[rjbinney]

Right, so it sounds like this is unpossible. One workaround is to "Copy Format" from OUTSIDE the CF grid and paste it in, but that creates such an ugly patchwork of CF logic that it would drive me out of my gourd.

Thanks, though.

(Any idea what the thinking is as to why CF doesn't allow different line weights?)

 

[Joe4]

Right, so it sounds like this is unpossible.

I wouldn't necessarily say that. Something probably can be done with VBA, if you can clearly define when you want it to run.
We might have to go overkill and make it run anytime a cell is selected. However, if you select a cell and change its formatting only, the code wouldn't run until you then move off of that cell.

If it was me, I would try to look for a different way of approaching this, namely, get rid of the manual formatting changes, and maybe make a selection box where you choose something instead of manually changing the color of cells. Then Conditional Formatting should work, as it can reference these other cells you are using to make the necessary selections.

 

[rjbinney]

Yeah, I can think of workarounds, but the "visual management" part is what makes it easy to use - you can instantly see where gaps are, from a distance (even us color-blind folk), but there are details if you need 'em.

I'm partial to:
"Light a man a fire, keep him warm for a night. Light a man afire, keep him warm for life!"

 

[Joe4]

Yeah, I can think of workarounds, but the "visual management" part is what makes it easy to use - you can instantly see where gaps are, from a distance (even us color-blind folk), but there are details if you need 'em.

You still use Conditional Formatting to make those visual changes, based on your selection.
And as a user, it is usually easier to pick an entry from a drop-down selection box than it is to have to manually add cell coloring, especially when you are doing multiple color that involves them having to change the color (and you have more control, as there are multiple grey options).