Conditional Formatting?

[sueshe]

I have an Excel spreadsheet set up as follows:
(dates formatted as Mmm)
Jan Feb Mar Apr etc......
Insurance 1 17 11 4 9
Insurance 2 1 2 1 0
Insurance 3 15 21 27 20 etc...

I have =today() in cell I2. I enter the numbers for the
previous month, for instance, I just entered April's
numbers. I need them to show as bold, blue for the
previous month, so, since I'm putting in April's numbers,
and we are in May, then that column should show bold
blue. Next month when I'm putting in May's numbers,
April should go back to non bold, black, and May's should
be blue/bold.

Hoping someone can help me with this. I posted this a while
back, but never could get it to work. Thanks for any help you
can give me!!!

 

[Luke M]

Starting in top left of table (assumed to be B2)
Format-conditional format, Formula is
=MONTH(B$1)+1=MONTH($I$2)
Set your format to bold font, blue.

Copy formatting to other cells. Will change all rows of appropriate column.

Note that your headers/dates MUST be entered as dates, as you described, and
not just the words "Jan", "Feb", etc.

 

[Conan Kelly]

sueshe,

With the example you gave, I'm going to assume:

--Date headings are in cells B1:E1 and are actual dates (date serial
numbers) that can be used in date calculations.

--"Insurance #" headings/labels are in A2:A4

--Data is in B2:E4


Solution:
- Select range B2:E4
- Pay attention to which cell is the active cell. Will be the the white
cell in the selection...most often the top left cell...usually is the cell
you started the selection from (one of the 4 corners)...changes if you hit
tab/enter while the range is selected.
- Format > Conditional Formatting...
- Change "Cell Value Is" to "Formula Is"
- Enter this formula, making sure to adjust the column reference in the
first MONTH() function to that of the active cell:
- =MONTH(B$1)=(MONTH($I$2)-1)
- Click the "Format..." button and choose your formatting.
- Click "OK"
- Click "OK"

TA-DA!!!

HTH,

Conan Kelly

 

[sueshe]

Ok, that worked perfectly....thank you very much!!!
Now, I have something similar, but for the quarter...
that I also need help with....
1st QTR 2nd QTR 3rd QTR 4th QTR YTD
1944 645 2589
334 111 445
223 63 286
201 55 256
2702 874 0 0 3576

This is May, but I'm inputting for April, which is in
the 2nd quarter, is there a way to bold/blue for the
quarter that I'm inputting for? Would stay bold/blue
for each quarter (for instance, 2nd quarter would stay
blue until June's numbers were entered in July), then
when August's starts, 3rd QTR would then be bold/blue.


Thanks again so much, that was exactly what I needed.

 

[Luke M]

For this to work, your labels need to be 1, 2, 3, 4 (coulld use a hidden row,
if desired)
Format formula becomes

=B$1=INT((I2-1)/3)+1

I am curious as to how you figure that August is start of 3rd Qtr. Usual
breakdown of quarters is Jan-Mar, Apr-Jun, Jul-Sep, Oct-Dec.

 

[Tasha]

oops Thanks, I'll try the formula!!!

For this to work, your labels need to be 1, 2, 3, 4 (coulld use a hidden row,
if desired)
Format formula becomes

=B$1=INT((I2-1)/3)+1

I am curious as to how you figure that August is start of 3rd Qtr. Usual
breakdown of quarters is Jan-Mar, Apr-Jun, Jul-Sep, Oct-Dec.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

 

[Tasha]

I tried your formula, but got an error, the error highlights the three in the
formula.....maybe I'm doing something wrong?

formula I used =P$3=INT($i$2-1)/3)+1

P Q R S
(numbers 1,2,3,4 below are hidden)
1 2 3 4 row 3
row 4
QTR & YTD Totals row 5
1st QTR 2nd QTR 3rd QTR 4th QTR row 6
1944 645 row 7
334 111 row 8
223 63 row 9
201 55 row 10
2702 874 0 0 row 11

 

[David Biddulph]

Yes, you are doing something wrong.
Why not try the formula that was suggested, instead?
Hint: Parentheses come in pairs.

 

[Tasha]

there's no need to be nasty. I did put the 'pair' of parentheses in, I just
didn't type it into this post right. I still got the same error. I am
looking for some help, not somebody to put me down because I don't know how
to do it. Thanks for your help, I guess, but am looking for someone that can
actually 'help' me and not berate me for trying.

 

[David Biddulph]

Sadly none of us will be able to help you if what you post here isn't the
formula you are using. Don't try to retype, either from posts here into
your formula, or from your formula to the post here; use copy and paste.
If you post the actual formula you are using, then someone may be able to
help.

=P$3=INT($i$2-1)/3)+1 will flag an error and will highlight the 3
=B$1=INT((I2-1)/3)+1 won't flag and error and won't highlight the 3