 Help! complicated programming visual basic Index ‹ Excel ‹ Excel Programming
Author Message
joala   Posted: Sun Feb 19 09:02:00 CST 2006 Top
 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 Andrew   Posted: Sun Feb 19 09:02:00 CST 2006 Top
 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. Lisa   Posted: Sun Feb 19 12:23:27 CST 2006 Top
 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. > >  Index ‹ Excel ‹ Excel Programming