Conditional Formating - How to emphsize the current Month

[Negda]

I have a list of months as serial number (formated as Month and Year.
How I can illuminate the current month in 1 color, future months in
second color and past month in 3rd color?

Thanks,
Negda

 

[pdberger]

Negda -- Use conditional formatting as follows:

Select range of cells that you want to format. For our purposes here, we'll
assume they start at cell A1.
Click Format>Conditional Formatting

For the first condition, change 'Cell Value Is' to 'Formula Is', then enter
the formula:
=MONTH(A1)=MONTH(TODAY())
and enter the formatting you want.
Enter two more conditions, changing to 'Formula Is' with the following
formulas:
=MONTH(A1)<MONTH(TODAY())
=MONTH(A1)>MONTH(TODAY())

setting the formatting appropriately each time. Worked for me.

HTH

 

[T. Valko]

Try this...

Assume the dates are in the range A1:A10
Select the range A1:A10
Goto the menu Format>Conditional Formatting

Condition 1 (present month)
Formula Is: =MONTH(A1)=MONTH(NOW())
Click the Format button
Select the desired style(s)
OK

Click the Add button

Condition 2 (future months)
Formula Is: =MONTH(A1)>MONTH(NOW())
Click the Format button
Select the desired style(s)
OK

Click the Add button

Condition 3 (past months)
Formula Is: =AND(A1<>"",MONTH(A1)<MONTH(NOW()))
Click the Format button
Select the desired style(s)
OK out

 

[Negda]

Negda -- Use conditional formatting as follows:

Select range of cells that you want to format.  For our purposes here, we'll
assume they start at cell A1.  
Click Format>Conditional Formatting

For the first condition, change 'Cell Value Is' to 'Formula Is', then enter
the formula:
=MONTH(A1)=MONTH(TODAY())
and enter the formatting you want.
Enter two more conditions, changing to 'Formula Is' with the following
formulas:
=MONTH(A1)<MONTH(TODAY())
=MONTH(A1)>MONTH(TODAY())

setting the formatting appropriately each time.  Worked for me.

HTH






- Show quoted text -

great it works - but only for 1 year so i changed only the later
formulas as:
condition 2: A$1 < today()
condition 2: A$1 > today()

thank you very much (Inever used the 'formula is' option...)

Negda

 

[Negda]

Try this...

Assume the dates are in the range A1:A10
Select the range A1:A10
Goto the menu Format>Conditional Formatting

Condition 1 (present month)
Formula Is: =MONTH(A1)=MONTH(NOW())
Click the Format button
Select the desired style(s)
OK

Click the Add button

Condition 2 (future months)
Formula Is: =MONTH(A1)>MONTH(NOW())
Click the Format button
Select the desired style(s)
OK

Click the Add button

Condition 3 (past months)
Formula Is: =AND(A1<>"",MONTH(A1)<MONTH(NOW()))
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP







- Show quoted text -

Hi Biff, it works only for 1 year so, i changed it a bit and its ok
(until now i never used the 'Formula is ' option...)
what does it mean the 'AND' at the beginning of the past condition?

thank you
Negda

 

[T. Valko]

what does it mean the 'AND' at the beginning of the past condition?

If a cell is empty it will evaluate as month number 1 which could lead to
the format being applied to empty cells. The AND tests that the cells are
not empty so that the formatting is not applied to empty cells.


--
Biff
Microsoft Excel MVP

Try this...

Assume the dates are in the range A1:A10
Select the range A1:A10
Goto the menu Format>Conditional Formatting

Condition 1 (present month)
Formula Is: =MONTH(A1)=MONTH(NOW())
Click the Format button
Select the desired style(s)
OK

Click the Add button

Condition 2 (future months)
Formula Is: =MONTH(A1)>MONTH(NOW())
Click the Format button
Select the desired style(s)
OK

Click the Add button

Condition 3 (past months)
Formula Is: =AND(A1<>"",MONTH(A1)<MONTH(NOW()))
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP







- Show quoted text -

Hi Biff, it works only for 1 year so, i changed it a bit and its ok
(until now i never used the 'Formula is ' option...)
what does it mean the 'AND' at the beginning of the past condition?

thank you
Negda

 

[Negda]

If a cell is empty it will evaluate as month number 1 which could lead to
the format being applied to empty cells. The AND tests that the cells are
not empty so that the formatting is not applied to empty cells.

--
Biff
Microsoft Excel MVP











Hi Biff, it works only for 1 year so, i changed it a bit and its ok
(until now i never used the 'Formula is ' option...)
what does it mean the 'AND' at the beginning of the past condition?

thank you
Negda- Hide quoted text -

- Show quoted text -

thanks again, Biff
Negda