Excel Conditional Formatting and Non VBA solutions

[sxschech]

Inherited an excel file. Was pretty messed up and have fixed many of the data issues. The person who transferred the file to me is no longer here and the instructions (rules?) were as follows:

If are asked to:
[ul]
[li]add a row, highlight the range in yellow[/li]
[li]"delete" a row, highlight the range in red[/li]
[li]change a value, highlight the cell(s) in blue[/li]
[/ul]

On the last Monday of month, save and distribute the file
Reset for new month by removing all the coloring
Any new request that comes in will fall under the new month even though the actual date is still under the current month

I started thinking if I add some date cols, then I could use conditional formatting to handle the first two scenarios. It seemed to work well, then the issues came up, since the month of the file is generally one week before end of month, I can't enter today's date and have it highlight as a new record because April is not equal to May. Because of this, added an additional col called Submittal Month and then I put in a cell in B1 and entered the date representing the month so now can key off that to affect the conditional formatting rather than the actual month.

Remaining issues: (can they be achieved without turning this into vba or macro enable workbook)

1.
As part of the review process a copy of the file is sent to another team member who checks one of the date cols and if it needs to be updated, enters the date and colors the cell orange so that it is easily identified. Due to conditional formatting, that takes precedence and the orange color no longer is displayable. My thought was to either add yet another col (undesirable due to scrolling and an extra step on the user's part) or what I ended up doing is altering the conditional format formula to "skip" the col that the other person would be editing so that it can then be manually colored. Visually, this does not look nice, but if that is the only way without code or extra intervention, guess we can live with it.

2.
For conditional formatting on cells that have been edited, any thoughts?

For example:

Asked to please Change Row 367 from
[tt]Col Bescription Col CartNumber[/tt]
[pre]Paint Cyan 4454[/pre]

To

[tt]Col Bescription Col CartNumber[/tt]
[pre]Paint Blue 4455[/pre]

Once I have edited B367 and C367 then those two cells should be highlighted blue until we move on to the next month and reset. I imagine would have to add a col to identify the cells, but is beginning to sound vba-ish.

 

[SkipVought]

Hi,

Regarding Conditional Formatting, you need a date column and a new column...

add a row, highlight the range in yellow--put today's date in the date column and a value in the new column.
"delete" a row, highlight the range in red--so where does the RED go if the row is deleted?
change a value, highlight the cell(s) in blue--put today's date in the date column.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[sxschech]

Base on your response: Sorry if my explanation wasn't clear (too much background?)

I have already created the conditional formatting and the (date and new col) and it is doing what it was intended, for the yellow and red and have a work around (not ideal of skipping the date col) so it can be manually colored.

Regarding the blue coloring, it will need more than a date in order to determine which cell to color or are use saying that if I have 10 cols, I have to create an additional 10 date cols to handle each cell that potentially might be changed?
I can't key off of "today's date" due to the month overlap and have to use a generic date for the month the changes apply to. Since we are still in the month of April, and currently the file/report is for May I need to have both April and May dates and only those dates in April that relate to May rather than April.

If explanation still murkey, let me know.

 

[SkipVought]

manually colored.

???Why???

A visual example would help. But YES, under the conditions you're describing, you need a date for each change.

This is where VBA could be a better choice for the BLUE color. Id use a Hidden or Very Hidden sheet to store the date, using the same cell reference, driven by the Worksheet_Change Event. You must also address month-beginning initialization.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

>"delete" a row, highlight the range in red
I take you don't really want to 'delete' the row and this row to be gone, just to 'mark it' as being deleted by 'highlight [the row] in red'

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[sxschech]

Andy, correct, not deleting in the sheet. The previous person had been deleting, but the new instructions are to keep it in the list and just show it as deleted, so I have placed conditional formatting in the curent period show as red and then previous period will show as strike through.

Skip, I have attached a sample. Hopefully it will better express what I wrote. However, based on my understanding of your message, looks like I may have to go the vba route to make it work. I'm concerned that people may have security issues with receiving code enabled files. Most around here don't do anything beyond the very basic stuff and will think there is a problem after 3 years of getting this file and having to deal with a security popup. They may be suspicious.

Or maybe I should scrap conditional formatting and use vba to color the cells and save a copy of the file to a standard xlsx format?


Tab Step6Conditional Format is how it looks now (second tab)
Tab Step6Not Conditionally Formatted is how it should look (third tab)

Notice in cell H6 on the second tab is yellow and blank. When I receive it back from the other team member, it should look like cell H6 on the third tab (orange and has a date)

The items on the third tab rows 8 and 9 are highlighted blue because I changed them from what they show on the second tab.

Once we move on to the next month, then all the coloring should be gone with the exception of the strike through.

 

[SkipVought]

Thanks for providing a workbook as an example.

So what are ALL the CF criteria for CHANGING values that are displayed on the Step6NotConditionalFormat?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

We are at a disadvantage,

1) not knowing or understanding the purpose of these requirements
2) not knowing or understanding the process and frequency
3) not knowing or understanding the level of security or impact this has on your business

This smells like a chain-of-custody type of process. Well this could be a low-risk thing or high-risk. Depending on the level of risk, it could be done in Excel, as you intend OR it should be totally automated with fail-safe procedures to track each change to the person, date & time, previous state and present state or something in the middle???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

Just a suggestion...
Add another column:
Status[ul][li]New - add a row, highlight the row in yellow[/li]
[li]Deleted - "delete" a row, highlight the row in red[/li]
[li]Changed / Modified - change a value, highlight the cell(s) in blue[/li]
[/ul]
Easy for Conditional Formatting to do at least first two Statuses...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Changed / Modified - change a value, highlight the cell(s) in blue

But it appears to be more complicated than that.

On a row you might have multiple changes, and maybe multiple colors. So you must accommodate row, column and color, multiple times. And I don't think that the OP wants the operator having to add those notations.

But it seem that currently, each person colors their own changed cells. Why not simply continue that.

Then just code an initialization procedure to clear all such color notations and strike-through "deleted" rows.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[sxschech]

Based on the discussion so far, I'll see how it goes with the level of automation I currently put into the sheet since it is about 90% of the way there. That way, hopefully all I have to remember with each "new month" to only need to search for the blue items to clear out.

Too bad that you can't manually override conditional formatting simply by changing the color of the cell.
Sometimes what seems the simplest thing on the surface is actually complicated to implement.

Thanks to both of you for your input.

 

[Andrzejek]

>seems the simplest thing on the surface is actually complicated to implement
... or sometimes we use just the wrong tool for the job.
Just my opinion, but maybe a simple data base with some basic GUI would do a lot better job...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[sxschech]

Of course my first instinct was to use Access as that is what I used for most things, but due to the state of how the original spreadsheet was when I received it and still having the issue of identifying the different colors and that no one else is using a database or would be willing to (that goes double for the external receivers of this spreadsheet), this is the result.

 

[strongm]

Unfortunately, can't see a simple way to do this in excel without at least a small amount of VBA or at least some manual intervention.

For example' Skip's suggestion of using a hidden sheet would work for the blue cells, but we don't have to track dates in it. All you need to do when creating a new month worksheet is to duplicate the existing sheet to a new sheet in the workbook; let's call it "Mirror" - hide it or very hide it if wished. Then all we need is a conditional rule that applies to the same ranges as your existing rules as follows: =Mirror!A4<>A4

Here's your example workbook with this included. Used the original Step6ConditionalFormat and copied it in its entirety to Mirror. Added the new conditional rule to Step6ConditionalFormat and then modified cells E8, B9, E9 as per "Step6Not ConditionallyFormated" - and, as we can see, we get the required highlighting.

 

[sxschech]

Thanks for the example workbook. I tried it out and saw how it works. However, because we are also adding rows, it causes the new rows to become blue since the new row(s) won't match the mirror.

I had another thought, if I add a new col <P>, can the conditional format use that to derive the fields to apply the color to?

After thinking it through this probably won't work either, but putting out there in case someone has an idea of the possibility.

For example if col <P> contains <E> then the conditional formula would evaluate that along with the row and look something like this?

[tt]=MONTH($B$1)&YEAR($B$1)=MONTH($L8)&YEAR($L8) AND len(P8) > 0 then Color Cell (E8) -->[the cell reference of the value in cell P8 concatenated with the row number][/tt]

I guess that won't work because if as in the case of <P9> we are trying to color both cell <B9> and <E9>.

I have reattached the original file with the inserted col P, no other changes were made.

 

[sxschech]

Looks like I didn't get the attachment loaded in the previous post.

 

[strongm]

>adding rows,

Yep, me not paying attention to the full requirements. I revert to my agreement with Skip: this will need a little VBA

 

[sxschech]

Appreciate your looking at it. thanks.