Help! complicated programming visual basic

Excel Programming >> Help! complicated programming visual basic

I hope someone can help and I hope Iâ??ve been clear. Thank you in advance for
your advice. It is much appreciated.

If cell A4 is a particular month (mmm) or my calculation in that field is:
=text(date)(year)(now()),month(now()),0),â??mmmâ??

and F4:Q4 are months shown as mmm (i.e F4 is Jul , G4 is Aug , H4 is Sep
etc.)

For example, A4 is showing JAN now but the actual month is Feb (regardless
of the day in February)

I need to count the cells that have a positive value in them for ONLY the
previous 6 months and place that count in the proper row in Col R. So if
its Feb then count G:L or Sep-Jan.

To determine the rows that need to have this count applied to it I have code
in another module called color format that uses the follow to determine which
rows. It is:

' Check length of cell in column A
If Len(cell.Offset(0, -(ncol - 1))) = 4 Then
Count ???????????

Then I need to look only in the cells in the rows for the previous 6 months
and do the following:

If R39:R500 is >=5 then background color in col R for that corresponding row
is 4

If R39:R500 is =4 and if at least one cell in F9:Q9 is >=$3000 then
background color in col R for that corresponding row is 4

If R39:R500 is =4 and if NO cell is >=$3000 then background color in col R
for that corresponding row is 35

If R39:R500 is =3 and if at least one cell in F9:Q9 is >=$3000 then
background color in col R for that corresponding row is 35

If R39:R500 is =3 and if NO cell is >=$3000 then background color in col R
for that corresponding row is 36

If R39:R500 is =2 and if at least one cell in F9:Q9 is >=$3000 then
background color in col R for that corresponding row is 36

If R39:R500 is <=2 and if NO cell is >=$3000 then background color in col R
for that corresponding row is 3

If R39:R500 is 0 or BLANK then background color in col R for that
corresponding row is 3

Also if count is 0, Iâ??d prefer just a blank with background of 3 instead of 0.

When A4 switches to Feb and the actual month is Mar. I need it to override
the previous background colors and put the new corresponding background
colors in its place.
Another alternative if it makes the vba any easier is in F4:Q4 I have
conditional formatting so that when A4 is JAN, â??JANâ?? in L4 has background
highlighted in bright yellow and font black and if not = to A4 then Black
background, white font. So when I open my spreadsheet in February, JAN is
highlighted at L4 not February.

Thanks again for your help.

Excel444

Excel Programming >> Help! complicated programming visual basic Lisa, you have some quite detailed requirements here, but it's
not clear (to me at least) exactly what it is that you need help with.
This might explain why you haven't had any replies yet. The basic
idea seems to be:

If (some condition is met) Then
(set background colour to X)
ElseIf (some other condition is met) Then
(set background colour to Y)
...... several more tests....
End If

If you could clarify what it is that you need to know I'm sure
someone will be able to help. Is it testing the conditions?
Setting the colours? Something else?

BTW the formula you give of
=text(date)(year)(now()),month(now()),0),"mmm"
should presumably be
=TEXT(DATE(YEAR(NOW()),MONTH(NOW()),0),"mmm")

Andrew

> I hope someone can help and I hope I've been clear. Thank you in advance for
> your advice. It is much appreciated.
>
> If cell A4 is a particular month (mmm) or my calculation in that field is:
> =text(date)(year)(now()),month(now()),0),"mmm"
>
> and F4:Q4 are months shown as mmm (i.e F4 is Jul , G4 is Aug , H4 is Sep
> etc.)
>
> For example, A4 is showing JAN now but the actual month is Feb (regardless
> of the day in February)
>
> I need to count the cells that have a positive value in them for ONLY the
> previous 6 months and place that count in the proper row in Col R. So if
> its Feb then count G:L or Sep-Jan.
>
> To determine the rows that need to have this count applied to it I have code
> in another module called color format that uses the follow to determine which
> rows. It is:
>
> ' Check length of cell in column A
> If Len(cell.Offset(0, -(ncol - 1))) = 4 Then
> Count ???????????
>
> Then I need to look only in the cells in the rows for the previous 6 months
> and do the following:
>
> If R39:R500 is >=5 then background color in col R for that corresponding row
> is 4
>
> If R39:R500 is =4 and if at least one cell in F9:Q9 is >=$3000 then
> background color in col R for that corresponding row is 4
>
> If R39:R500 is =4 and if NO cell is >=$3000 then background color in col R
> for that corresponding row is 35
>
> If R39:R500 is =3 and if at least one cell in F9:Q9 is >=$3000 then
> background color in col R for that corresponding row is 35
>
> If R39:R500 is =3 and if NO cell is >=$3000 then background color in col R
> for that corresponding row is 36
>
> If R39:R500 is =2 and if at least one cell in F9:Q9 is >=$3000 then
> background color in col R for that corresponding row is 36
>
> If R39:R500 is <=2 and if NO cell is >=$3000 then background color in col R
> for that corresponding row is 3
>
> If R39:R500 is 0 or BLANK then background color in col R for that
> corresponding row is 3
>
> Also if count is 0, I'd prefer just a blank with background of 3 instead of 0.
>
> When A4 switches to Feb and the actual month is Mar. I need it to override
> the previous background colors and put the new corresponding background
> colors in its place.
> Another alternative if it makes the vba any easier is in F4:Q4 I have
> conditional formatting so that when A4 is JAN, "JAN" in L4 has background
> highlighted in bright yellow and font black and if not = to A4 then Black
> background, white font. So when I open my spreadsheet in February, JAN is
> highlighted at L4 not February.
>
> Thanks again for your help.

Excel Programming >> Help! complicated programming visual basic Thanks Andrew. The requirements do seem complicated but you are write about
the basic "if some condition is met then.... Basically if a cell in F4:Q4
is current month & if col A has a 4 digit # in it then count the # of cells
in the previous 6 months in that row that have a positive # and put that
figure in the corresponding cell in col R. Then apply the if then statements
I have below to apply a background color in the corresponding cell in col R.
Does that help? Thank you.

> Lisa, you have some quite detailed requirements here, but it's
> not clear (to me at least) exactly what it is that you need help with.
> This might explain why you haven't had any replies yet. The basic
> idea seems to be:
>
> If (some condition is met) Then
> (set background colour to X)
> ElseIf (some other condition is met) Then
> (set background colour to Y)
> ....... several more tests....
> End If
>
> If you could clarify what it is that you need to know I'm sure
> someone will be able to help. Is it testing the conditions?
> Setting the colours? Something else?
>
>
> BTW the formula you give of
> =text(date)(year)(now()),month(now()),0),"mmm"
> should presumably be
> =TEXT(DATE(YEAR(NOW()),MONTH(NOW()),0),"mmm")
>
> Andrew
>
>

> > I hope someone can help and I hope I've been clear. Thank you in advance for
> > your advice. It is much appreciated.
> >
> > If cell A4 is a particular month (mmm) or my calculation in that field is:
> > =text(date)(year)(now()),month(now()),0),"mmm"
> >
> > and F4:Q4 are months shown as mmm (i.e F4 is Jul , G4 is Aug , H4 is Sep
> > etc.)
> >
> > For example, A4 is showing JAN now but the actual month is Feb (regardless
> > of the day in February)
> >
> > I need to count the cells that have a positive value in them for ONLY the
> > previous 6 months and place that count in the proper row in Col R. So if
> > its Feb then count G:L or Sep-Jan.
> >
> > To determine the rows that need to have this count applied to it I have code
> > in another module called color format that uses the follow to determine which
> > rows. It is:
> >
> > ' Check length of cell in column A
> > If Len(cell.Offset(0, -(ncol - 1))) = 4 Then
> > Count ???????????
> >
> > Then I need to look only in the cells in the rows for the previous 6 months
> > and do the following:
> >
> > If R39:R500 is >=5 then background color in col R for that corresponding row
> > is 4
> >
> > If R39:R500 is =4 and if at least one cell in F9:Q9 is >=$3000 then
> > background color in col R for that corresponding row is 4
> >
> > If R39:R500 is =4 and if NO cell is >=$3000 then background color in col R
> > for that corresponding row is 35
> >
> > If R39:R500 is =3 and if at least one cell in F9:Q9 is >=$3000 then
> > background color in col R for that corresponding row is 35
> >
> > If R39:R500 is =3 and if NO cell is >=$3000 then background color in col R
> > for that corresponding row is 36
> >
> > If R39:R500 is =2 and if at least one cell in F9:Q9 is >=$3000 then
> > background color in col R for that corresponding row is 36
> >
> > If R39:R500 is <=2 and if NO cell is >=$3000 then background color in col R
> > for that corresponding row is 3
> >
> > If R39:R500 is 0 or BLANK then background color in col R for that
> > corresponding row is 3
> >
> > Also if count is 0, I'd prefer just a blank with background of 3 instead of 0.
> >
> > When A4 switches to Feb and the actual month is Mar. I need it to override
> > the previous background colors and put the new corresponding background
> > colors in its place.
> > Another alternative if it makes the vba any easier is in F4:Q4 I have
> > conditional formatting so that when A4 is JAN, "JAN" in L4 has background
> > highlighted in bright yellow and font black and if not = to A4 then Black
> > background, white font. So when I open my spreadsheet in February, JAN is
> > highlighted at L4 not February.
> >
> > Thanks again for your help.
>
>