Conditional Formatting based on WHEN I enter data

[How2ExcelGood]

I have a workbook where I enter the same data each time I have a client show up for a session. I have to fill in the same 10 values based on what they say to me each time. I like to leave their values from the last session so I can remember what they were talking about. I am wondering if there is a way to have the same 10 cells light up each day and then go back to not being lit up based on WHEN I have entered new data?

So I would start with data in the cell, then I would want the cell to light up based on another cell saying "Show", then I would like the cell to go back to normal color after I enter NEW information into the cell. Does that make sense?

How can I do this?

***Excel 2013****

 

[Joe4]

Welcome to the Board!

I am not entirely clear on what your data looks like, and how exactly you want this to work. I think it would be very beneficial for us if you could post a sample of your data, and show us what you want to happen.

Note that MrExcel has a tool called “XL2BB” that will allow you to post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: https://www.mrexcel.com/board/excel-articles/xl2bb-excel-range-to-bbcode

There is also a “Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[How2ExcelGood]

I have a workbook where I enter the same data each time I have a client show up for a session. I have to fill in the same 10 values based on what they say to me each time. I like to leave their values from the last session so I can remember what they were talking about. I am wondering if there is a way to have the same 10 cells light up each day and then go back to not being lit up based on WHEN I have entered new data?

So I would start with data in the cell, then I would want the cell to light up based on another cell saying "Show", then I would like the cell to go back to normal color after I enter NEW information into the cell. Does that make sense?

How can I do this?

***Excel 2013****

I can't figure out the tool you are referencing, so instead I am going to show you a picture of what I am talking about.

So in row 1 you can see that the DOS is today. When I haven't entered data into certain columns, they light up a fun color to remind me I need to fill in that data. In row 2 you can see that the DOS is not today (it's 12/10/2020), but now the columns are not lighting up to ask me to fill in data (they are supposed to light up based on what is in the "Show?" column, no matter the date. Is there a way to make the columns re-light up even though there is text in them, then for them to not light up after I have entered new text today?

 

[Joe4]

So let's see if I have this straight. I think what you are saying is that if the following conditions exist in a row:
- DOS column has today's date
- Show column says "Show"
- Certain other columns are blank
Then those certain other columns should change color.

Is that correct? If so, then you would just use Conditional Formatting in those cells you want colored. Their formula would look like this (this example for the cell G2).
So, you would select cell G2, go to Conditional Formatting, choose the formula option (last one), and enter this CF formula:

=AND($E2=TODAY(),$F2="Show",G2="")

and then choose your desired formatting color.

To apply this to multiple cells at once, just select all the cells you want to apply this to first, and write the formula as it pertains to the first cell in your selection, and as long as you have used the proper mix of absolute and cell referencing (the location of the "$" signs in my formula), it should automatically adjust and work for all the other cells in your selection.
Note that the placement of the "$" in my formulas. They HAVE to be exactly where I show them (before the E and F column reference), and nowhere else in the formula.
See here for an explanation on this: Excel Mixed reference | Exceljet

 

[How2ExcelGood]

So let's see if I have this straight. I think what you are saying is that if the following conditions exist in a row:
- DOS column has today's date
- Show column says "Show"
- Certain other columns are blank
Then those certain other columns should change color.

Is that correct? If so, then you would just use Conditional Formatting in those cells you want colored. Their formula would look like this (this example for the cell G2).
So, you would select cell G2, go to Conditional Formatting, choose the formula option (last one), and enter this CF formula:

=AND($E2=TODAY(),$F2="Show",G2="")

and then choose your desired formatting color.

To apply this to multiple cells at once, just select all the cells you want to apply this to first, and write the formula as it pertains to the first cell in your selection, and as long as you have used the proper mix of absolute and cell referencing (the location of the "$" signs in my formula), it should automatically adjust and work for all the other cells in your selection.
Note that the placement of the "$" in my formulas. They HAVE to be exactly where I show them (before the E and F column reference), and nowhere else in the formula.
See here for an explanation on this: Excel Mixed reference | Exceljet

So that's what I currently have. Now I want to take it a step further.

Let's say yesterday I filled in all the blank columns and now they are all filled with data.

Today I want to go back in and fill in new data, but I cannot remember which columns need to be filled each day. I also don't want to delete yesterday's data, because some of that might be good to read as I fill in new data.

Is there a way to get those columns to light back up even though they are already filled in? I would like them lit up before I fill them in today, then once I add new data for the day, to stop being lit.

 

[How2ExcelGood]

So that's what I currently have. Now I want to take it a step further.

Let's say yesterday I filled in all the blank columns and now they are all filled with data.

Today I want to go back in and fill in new data, but I cannot remember which columns need to be filled each day. I also don't want to delete yesterday's data, because some of that might be good to read as I fill in new data.

Is there a way to get those columns to light back up even though they are already filled in? I would like them lit up before I fill them in today, then once I add new data for the day, to stop being lit.

So in the image I already sent, we are looking at row 3 (which has data already filled in). I want those same cells that are lit up in row 2 based on "Show" being selected, to light up in row 3 even though there is already data there. Then I want that cell to turn back to blue (which is the default color) after I have typed in new information THAT DAY.

Basically, I am trying to get time-based data entry conditional formatting.

 

[Joe4]

The issue is that Conditional Formatting can only evaluate what is currently in (or not in) the cells.
It cannot tell what was in there before, or if something changed. It can only evaluate based on what is in (or not in) the cells RIGHT NOW.

If you want to try comparing what is in cells with what was previously in those cells, you are going to need to use VBA.

 

[How2ExcelGood]

The issue is that Conditional Formatting can only evaluate what is currently in (or not in) the cells.
It cannot tell what was in there before, or if something changed. It can only evaluate based on what is in (or not in) the cells RIGHT NOW.

If you want to try comparing what is in cells with what was previously in those cells, you are going to need to use VBA.

Ahhh okay, how might I use VBA to do what I want?

 

[Joe4]

Ahhh okay, how might I use VBA to do what I want?

It is going to be a little tricky, because you need multiple VBA procedures in order to capture previous values, especially if you want previous values of multiple cells.
And to be 100% honest with you, I still find your explanation a big confusing. I would like to think that there is probably a better way to accomplish what you want, but I don't understand the details of what you are trying to do well enough to come up with a good suggestion for you.

Here are some links that shows you how you can use VBA to compare new values entered to what the previous value was.

Warning, if you are fairly new to VBA, this method may seem a bit overwhelming:

How to remember or save previous cell value of a changed cell in Excel?

www.extendoffice.com

Here is one that is not quite as complex:

Excel VBA get value before Worksheet_Change event

This page explains how to get value before Worksheet_Change event using Excel VBA and how to trigger Macro when value changes

access-excel.tips