Conditional Formatting?

  • Thread starter Thread starter sueshe
  • Start date Start date
S

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!!!
 
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.
 
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
 
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.
 
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.
 
oops :) Thanks, I'll try the formula!!!

Luke M said:
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!*
 
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
 
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.
 
Back
Top