Conditional Formating/Date Counter

[Smoakstack]

Hello all. I am sure this can be done without VBA, but am looking to highlight past dates red, current date green. Problem is I have a standard excel sheet. ie: A3:G3 shows Sunday through Saturday accordingly. I then spaced 5 cells down per each day that is numbered (not by formula) to correspond to day. Is there a way to make a counter so I can see this on excel?

 

[Akashwani]

Hi,

If you post some sample data you will probably get the answer you seek.
When you say "I then spaced 5 cells down per each day that is numbered (not by formula) to correspond to day" what do you mean, something like this?

Excel Workbook
	A	B	C	D	E	F	G
1	*	*	*	04/06/2011	*	*	*
2	*	*	*	*	*	*	*
3	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
4	29/05/2011	30/05/2011	31/05/2011	01/06/2011	02/06/2011	03/06/2011	04/06/2011
5	05/06/2011	06/06/2011	07/06/2011	08/06/2011	09/06/2011	10/06/2011	11/06/2011
6	12/06/2011	13/06/2011	14/06/2011	15/06/2011	16/06/2011	17/06/2011	18/06/2011
7	19/06/2011	20/06/2011	21/06/2011	22/06/2011	23/06/2011	24/06/2011	25/06/2011
8	26/06/2011	27/06/2011	28/06/2011	29/06/2011	30/06/2011	01/07/2011	02/07/2011
Sheet2

Ak

 

[Smoakstack]

This is what I have to begin with.

June

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 12pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 142px"><COL style="WIDTH: 142px"><COL style="WIDTH: 142px"><COL style="WIDTH: 142px"><COL style="WIDTH: 142px"><COL style="WIDTH: 142px"><COL style="WIDTH: 142px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center" colSpan=6>June</TD><TD style="TEXT-ALIGN: right">2011</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="BACKGROUND-COLOR: #ffff99">Sunday</TD><TD style="BACKGROUND-COLOR: #ffff99">Monday</TD><TD style="BACKGROUND-COLOR: #ffff99">Tuesday</TD><TD style="BACKGROUND-COLOR: #ffff99">Wednesday</TD><TD style="BACKGROUND-COLOR: #ffff99">Thursday</TD><TD style="BACKGROUND-COLOR: #ffff99">Friday</TD><TD style="BACKGROUND-COLOR: #ffff99">Saturday</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">11</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right">17</TD><TD style="TEXT-ALIGN: right">18</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: right">19</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">21</TD><TD style="TEXT-ALIGN: right">22</TD><TD style="TEXT-ALIGN: right">23</TD><TD style="TEXT-ALIGN: right">24</TD><TD style="TEXT-ALIGN: right">25</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="TEXT-ALIGN: right">26</TD><TD style="TEXT-ALIGN: right">27</TD><TD style="TEXT-ALIGN: right">28</TD><TD style="TEXT-ALIGN: right">29</TD><TD style="TEXT-ALIGN: right">30</TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">29</TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">31</TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">32</TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">33</TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD><TD style="BACKGROUND-COLOR: #666699"> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>G1</TD><TD>=YEAR(TODAY())</TD></TR><TR><TD>F4</TD><TD>=IFERROR(IF(DAY(TODAY())=3, DAY(TODAY()),3),3)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


What I would like is for today's date to be highlighted in green and all of the past dates highlighted in red. Essentially I am making myself my own appointment book.

 

[Smoakstack]

The issue I am having is having the space below the date be highlighted the same color as the date. ie: I have B9 highlighted green, but B10:B13 will highlight red because the value is less than =day(today())

 

[Akashwani]

Hi,

I don't have a solution for you, sorry, is this what you are trying to achieve?...

Excel Workbook
	A	B	C	D	E	F	G
1	June						2011
2	*	*	*	*	*	*	*
3	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
4	*	*	*	1	2	3	4
5	*	*	*	*	*	*	*
6	*	*	*	*	*	*	*
7	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*
9	5	6	7	8	9	10	11
10	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*
12	*	*	*	*	*	*	*
13	*	*	*	*	*	*	*
14	12	13	14	15	16	17	18
15	*	*	*	*	*	*	*
16	*	*	*	*	*	*	*
17	*	*	*	*	*	*	*
18	*	*	*	*	*	*	*
19	19	20	21	22	23	24	25
20	*	*	*	*	*	*	*
21	*	*	*	*	*	*	*
22	*	*	*	*	*	*	*
23	*	*	*	*	*	*	*
24	26	27	28	29	30	*	*
25	*	*	*	*	*	*	*
26	*	*	*	*	*	*	*
27	*	*	*	*	*	*	*
28	*	*	*	*	*	*	*
29	*	*	*	*	*	*	*
30	*	*	*	*	*	*	*
31	*	*	*	*	*	*	*
32	*	*	*	*	*	*	*
33	*	*	*	*	*	*	*
June

If the above is your desired expected result then I have no idea how this can be achieved, sorry.

Ak

 

[Smoakstack]

yes, that is what I am trying to achieve. It will eventually be an appointment/financial/everything else I need it to be calender.

 

[Smoakstack]

To give you an idea of where I am heading towards. But I would like for the four rows beneath the highlight date to be highlighted as well.

You have exactly what I am looking for.

Excel Workbook
	A	B	C	D	E	F	G
1	June						2011
2
3	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
4				1	2	3	4
5				$869.00	$0.00	$10,000.00	$0.00
6
7
8
9	5	6	7	8	9	10	11
10	$0.00	$2.14	$0.00	$0.00	$0.00	$0.00	$0.00
11
12
13
14	12	13	14	15	16	17	18
15	$0.00	$0.00	$0.00	$0.00	$0.00	$10,000.00	$0.00
16
17
18
19	19	20	21	22	23	24	25
20	$0.00	$0.00	$0.00	$483.42	$0.00	$44.04	$0.00
21
22
23
24	26	27	28	29	30
25	$0.00	$305.00	$0.00	$0.00	$0.00
26
27
28
29
30
31
32
33
34
35				Gain	$20,000.00
36				Loss	$1,703.60
37				Total	$18,296.40
June

 

[Smoakstack]

Would there be a way to do this with VB on a workbook open mod?

I was thinking along the lines of checking for the date and setting format 4 lines below it to be that color? Thoughts?

 

[Akashwani]

Hi,

See if you can find anything of help here....

http://www.cpearson.com/excel/colors.aspx

If not I suggest you start a new thread asking for a VBA conditional Formatting solution. You will need a VBA code to find the back ground colour of a cell and fill the next 4 cells down based on that colour only IF the cell background (fill) colour is Green or Red. The VBA code would probably have to be run every day or as a Worksheet Change Event.

You can highlight your first cell using these formulas...

=AND(A4<>"",A4< DAY(TODAY())) This is for the Red cell colour.

=AND(A4<>"",A4=DAY(TODAY())) This is for the Green cell colour.

Highlight your range from A4:G29 and apply the above formulas.

Sorry that I couldn't resolve this for you, but I do not write or understand VBA.

Good luck.

Ak

 

[Smoakstack]

thank you for your help