|
colorindex = xlnone on condition of month of year
Please see my code below. What is happening here is when the month changes
to Aug for example certain background colors which were colored in Jul will now be colored in Aug. Everything works except Now that we are in the month of Aug, Jul background colors are still colored and I now do not want those cells to have color anymore. The only coloring should be in current month, mmm. Can you help fix my code. Very new to this. Thank you. ' ALSO, HOW DO I MAKE THIS CODE ACTIVATE WHEN I OPEN THE WORKBOOK? 'HOW DO I CHANGE THIS CODE SO IT WORKS FOR SPECIFIC SHEETS (I.E. SHEET1, SHEET2, ETC.) BUT NOT ALL SHEETS 'WHEN I OPEN THE WORKBOOK? Sub CFormat() Dim rng As Range, cell As Range Dim ncol As Integer, lrow As Long Dim pcnt As Double, divisor As Double 'Sheet1 is sheet name ThisWorkbook.Worksheets("Sheet1").Activate ' Find column for current Month (add 5 to start in colum F onwards) ' Define "CurMonth" and "HdrMonths" in spreadsheet ncol = Application.WorksheetFunction.match(Range("a3"), Range("F3:Q3"), 0) + 5 ' Find last row of data in current month column lrow = Cells(Rows.Count, 1).End(xlUp).Row Range("F9:Q500").Select 'Clears the colours 'Selection.Interior.ColorIndex = xlNone 'THIS STATEMENT DELETES ALL CELL BACKGROUND COLOR WHICH 'IS NOT WHAT I NEED...I NEED SOMETHING LIKE THE FOLLOWING.. Select Case Case Is = Interior.ColorIndex = 4 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 35 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 36 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 7 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 54 Selection.Interior.ColorIndex = xlNone End Select ' Set range to cells for current month (WHICH IS CELL A3) starting row 20 Set rng = Range(Cells(20, ncol), Cells(lrow, ncol)) ' Set Divisor for current month divisor = Cells(5, ncol) ' Loop through all cells in range For Each cell In rng ' Check length of cell in column A If Len(cell.Offset(0, -(ncol - 1))) = 4 Then ' Calculate perecentage If Application.IsNumber(cell) Then ' Is this cell a number ? ' Calculate percentage pcnt = (cell / divisor) * 100 cell.Select ' Set colorindex based on percentage Select Case pcnt Case Is 100 Selection.Interior.ColorIndex = 4 Case Is = 90 Selection.Interior.ColorIndex = 35 Case Is = 80 Selection.Interior.ColorIndex = 36 Case Is = 70 Selection.Interior.ColorIndex = 7 Case Is = 1 Selection.Interior.ColorIndex = 54 Case Else Selection.Interior.ColorIndex = 3 End Select ' This colors the blank empty cells Else cell.Select Selection.Interior.ColorIndex = 3 End If End If Next cell End Sub |
colorindex = xlnone on condition of month of year
Jane,
Something like Dim MyCell For Each MyCell in Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is =4 Selection.Interior.ColorIndex = xlNone Etc End Select Next MyCell "Jane" wrote in message ... Please see my code below. What is happening here is when the month changes to Aug for example certain background colors which were colored in Jul will now be colored in Aug. Everything works except Now that we are in the month of Aug, Jul background colors are still colored and I now do not want those cells to have color anymore. The only coloring should be in current month, mmm. Can you help fix my code. Very new to this. Thank you. ' ALSO, HOW DO I MAKE THIS CODE ACTIVATE WHEN I OPEN THE WORKBOOK? 'HOW DO I CHANGE THIS CODE SO IT WORKS FOR SPECIFIC SHEETS (I.E. SHEET1, SHEET2, ETC.) BUT NOT ALL SHEETS 'WHEN I OPEN THE WORKBOOK? Sub CFormat() Dim rng As Range, cell As Range Dim ncol As Integer, lrow As Long Dim pcnt As Double, divisor As Double 'Sheet1 is sheet name ThisWorkbook.Worksheets("Sheet1").Activate ' Find column for current Month (add 5 to start in colum F onwards) ' Define "CurMonth" and "HdrMonths" in spreadsheet ncol = Application.WorksheetFunction.match(Range("a3"), Range("F3:Q3"), 0) + 5 ' Find last row of data in current month column lrow = Cells(Rows.Count, 1).End(xlUp).Row Range("F9:Q500").Select 'Clears the colours 'Selection.Interior.ColorIndex = xlNone 'THIS STATEMENT DELETES ALL CELL BACKGROUND COLOR WHICH 'IS NOT WHAT I NEED...I NEED SOMETHING LIKE THE FOLLOWING.. Select Case Case Is = Interior.ColorIndex = 4 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 35 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 36 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 7 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 54 Selection.Interior.ColorIndex = xlNone End Select ' Set range to cells for current month (WHICH IS CELL A3) starting row 20 Set rng = Range(Cells(20, ncol), Cells(lrow, ncol)) ' Set Divisor for current month divisor = Cells(5, ncol) ' Loop through all cells in range For Each cell In rng ' Check length of cell in column A If Len(cell.Offset(0, -(ncol - 1))) = 4 Then ' Calculate perecentage If Application.IsNumber(cell) Then ' Is this cell a number ? ' Calculate percentage pcnt = (cell / divisor) * 100 cell.Select ' Set colorindex based on percentage Select Case pcnt Case Is 100 Selection.Interior.ColorIndex = 4 Case Is = 90 Selection.Interior.ColorIndex = 35 Case Is = 80 Selection.Interior.ColorIndex = 36 Case Is = 70 Selection.Interior.ColorIndex = 7 Case Is = 1 Selection.Interior.ColorIndex = 54 Case Else Selection.Interior.ColorIndex = 3 End Select ' This colors the blank empty cells Else cell.Select Selection.Interior.ColorIndex = 3 End If End If Next cell End Sub |
colorindex = xlnone on condition of month of year
Try again!
Correct this time. Dim MyCell As Range For Each MyCell in Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is =4 MyCell.Interior.ColorIndex = xlNone Etc End Select Next MyCell Henry "Henry" wrote in message ... Jane, Something like "Jane" wrote in message ... Please see my code below. What is happening here is when the month changes to Aug for example certain background colors which were colored in Jul will now be colored in Aug. Everything works except Now that we are in the month of Aug, Jul background colors are still colored and I now do not want those cells to have color anymore. The only coloring should be in current month, mmm. Can you help fix my code. Very new to this. Thank you. ' ALSO, HOW DO I MAKE THIS CODE ACTIVATE WHEN I OPEN THE WORKBOOK? 'HOW DO I CHANGE THIS CODE SO IT WORKS FOR SPECIFIC SHEETS (I.E. SHEET1, SHEET2, ETC.) BUT NOT ALL SHEETS 'WHEN I OPEN THE WORKBOOK? Sub CFormat() Dim rng As Range, cell As Range Dim ncol As Integer, lrow As Long Dim pcnt As Double, divisor As Double 'Sheet1 is sheet name ThisWorkbook.Worksheets("Sheet1").Activate ' Find column for current Month (add 5 to start in colum F onwards) ' Define "CurMonth" and "HdrMonths" in spreadsheet ncol = Application.WorksheetFunction.match(Range("a3"), Range("F3:Q3"), 0) + 5 ' Find last row of data in current month column lrow = Cells(Rows.Count, 1).End(xlUp).Row Range("F9:Q500").Select 'Clears the colours 'Selection.Interior.ColorIndex = xlNone 'THIS STATEMENT DELETES ALL CELL BACKGROUND COLOR WHICH 'IS NOT WHAT I NEED...I NEED SOMETHING LIKE THE FOLLOWING.. Select Case Case Is = Interior.ColorIndex = 4 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 35 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 36 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 7 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 54 Selection.Interior.ColorIndex = xlNone End Select ' Set range to cells for current month (WHICH IS CELL A3) starting row 20 Set rng = Range(Cells(20, ncol), Cells(lrow, ncol)) ' Set Divisor for current month divisor = Cells(5, ncol) ' Loop through all cells in range For Each cell In rng ' Check length of cell in column A If Len(cell.Offset(0, -(ncol - 1))) = 4 Then ' Calculate perecentage If Application.IsNumber(cell) Then ' Is this cell a number ? ' Calculate percentage pcnt = (cell / divisor) * 100 cell.Select ' Set colorindex based on percentage Select Case pcnt Case Is 100 Selection.Interior.ColorIndex = 4 Case Is = 90 Selection.Interior.ColorIndex = 35 Case Is = 80 Selection.Interior.ColorIndex = 36 Case Is = 70 Selection.Interior.ColorIndex = 7 Case Is = 1 Selection.Interior.ColorIndex = 54 Case Else Selection.Interior.ColorIndex = 3 End Select ' This colors the blank empty cells Else cell.Select Selection.Interior.ColorIndex = 3 End If End If Next cell End Sub |
colorindex = xlnone on condition of month of year
Thanks so much for your reply. It didn't work completely. It didn't work on
cells with background of red and there was one cell that is light purple that didn't change. Do I have the wrong color code for light purple? This is what I added. Did I do it right? For Each MyCell In Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is = 4 MyCell.Interior.ColorIndex = xlNone End Select Next For Each MyCell In Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is = 35 MyCell.Interior.ColorIndex = xlNone End Select Next For Each MyCell In Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is = 36 MyCell.Interior.ColorIndex = xlNone End Select Next For Each MyCell In Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is = 7 MyCell.Interior.ColorIndex = xlNone End Select Next For Each MyCell In Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is = 54 MyCell.Interior.ColorIndex = xlNone End Select Next "Henry" wrote: Try again! Correct this time. Dim MyCell As Range For Each MyCell in Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is =4 MyCell.Interior.ColorIndex = xlNone Etc End Select Next MyCell Henry "Henry" wrote in message ... Jane, Something like "Jane" wrote in message ... Please see my code below. What is happening here is when the month changes to Aug for example certain background colors which were colored in Jul will now be colored in Aug. Everything works except Now that we are in the month of Aug, Jul background colors are still colored and I now do not want those cells to have color anymore. The only coloring should be in current month, mmm. Can you help fix my code. Very new to this. Thank you. ' ALSO, HOW DO I MAKE THIS CODE ACTIVATE WHEN I OPEN THE WORKBOOK? 'HOW DO I CHANGE THIS CODE SO IT WORKS FOR SPECIFIC SHEETS (I.E. SHEET1, SHEET2, ETC.) BUT NOT ALL SHEETS 'WHEN I OPEN THE WORKBOOK? Sub CFormat() Dim rng As Range, cell As Range Dim ncol As Integer, lrow As Long Dim pcnt As Double, divisor As Double 'Sheet1 is sheet name ThisWorkbook.Worksheets("Sheet1").Activate ' Find column for current Month (add 5 to start in colum F onwards) ' Define "CurMonth" and "HdrMonths" in spreadsheet ncol = Application.WorksheetFunction.match(Range("a3"), Range("F3:Q3"), 0) + 5 ' Find last row of data in current month column lrow = Cells(Rows.Count, 1).End(xlUp).Row Range("F9:Q500").Select 'Clears the colours 'Selection.Interior.ColorIndex = xlNone 'THIS STATEMENT DELETES ALL CELL BACKGROUND COLOR WHICH 'IS NOT WHAT I NEED...I NEED SOMETHING LIKE THE FOLLOWING.. Select Case Case Is = Interior.ColorIndex = 4 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 35 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 36 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 7 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 54 Selection.Interior.ColorIndex = xlNone End Select ' Set range to cells for current month (WHICH IS CELL A3) starting row 20 Set rng = Range(Cells(20, ncol), Cells(lrow, ncol)) ' Set Divisor for current month divisor = Cells(5, ncol) ' Loop through all cells in range For Each cell In rng ' Check length of cell in column A If Len(cell.Offset(0, -(ncol - 1))) = 4 Then ' Calculate perecentage If Application.IsNumber(cell) Then ' Is this cell a number ? ' Calculate percentage pcnt = (cell / divisor) * 100 cell.Select ' Set colorindex based on percentage Select Case pcnt Case Is 100 Selection.Interior.ColorIndex = 4 Case Is = 90 Selection.Interior.ColorIndex = 35 Case Is = 80 Selection.Interior.ColorIndex = 36 Case Is = 70 Selection.Interior.ColorIndex = 7 Case Is = 1 Selection.Interior.ColorIndex = 54 Case Else Selection.Interior.ColorIndex = 3 End Select ' This colors the blank empty cells Else cell.Select Selection.Interior.ColorIndex = 3 End If End If Next cell End Sub |
colorindex = xlnone on condition of month of year
Henry, I fixed my problem. It was on my side. What you gave me was great. I
realize I have another problem though which is, if we are in the month of "Aug" I am inputting month end information for "Jul" so month end "Jun" needs to have the background color change to "no color". Your help is appreciated. "Henry" wrote: Try again! Correct this time. Dim MyCell As Range For Each MyCell in Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is =4 MyCell.Interior.ColorIndex = xlNone Etc End Select Next MyCell Henry "Henry" wrote in message ... Jane, Something like "Jane" wrote in message ... Please see my code below. What is happening here is when the month changes to Aug for example certain background colors which were colored in Jul will now be colored in Aug. Everything works except Now that we are in the month of Aug, Jul background colors are still colored and I now do not want those cells to have color anymore. The only coloring should be in current month, mmm. Can you help fix my code. Very new to this. Thank you. ' ALSO, HOW DO I MAKE THIS CODE ACTIVATE WHEN I OPEN THE WORKBOOK? 'HOW DO I CHANGE THIS CODE SO IT WORKS FOR SPECIFIC SHEETS (I.E. SHEET1, SHEET2, ETC.) BUT NOT ALL SHEETS 'WHEN I OPEN THE WORKBOOK? Sub CFormat() Dim rng As Range, cell As Range Dim ncol As Integer, lrow As Long Dim pcnt As Double, divisor As Double 'Sheet1 is sheet name ThisWorkbook.Worksheets("Sheet1").Activate ' Find column for current Month (add 5 to start in colum F onwards) ' Define "CurMonth" and "HdrMonths" in spreadsheet ncol = Application.WorksheetFunction.match(Range("a3"), Range("F3:Q3"), 0) + 5 ' Find last row of data in current month column lrow = Cells(Rows.Count, 1).End(xlUp).Row Range("F9:Q500").Select 'Clears the colours 'Selection.Interior.ColorIndex = xlNone 'THIS STATEMENT DELETES ALL CELL BACKGROUND COLOR WHICH 'IS NOT WHAT I NEED...I NEED SOMETHING LIKE THE FOLLOWING.. Select Case Case Is = Interior.ColorIndex = 4 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 35 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 36 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 7 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 54 Selection.Interior.ColorIndex = xlNone End Select ' Set range to cells for current month (WHICH IS CELL A3) starting row 20 Set rng = Range(Cells(20, ncol), Cells(lrow, ncol)) ' Set Divisor for current month divisor = Cells(5, ncol) ' Loop through all cells in range For Each cell In rng ' Check length of cell in column A If Len(cell.Offset(0, -(ncol - 1))) = 4 Then ' Calculate perecentage If Application.IsNumber(cell) Then ' Is this cell a number ? ' Calculate percentage pcnt = (cell / divisor) * 100 cell.Select ' Set colorindex based on percentage Select Case pcnt Case Is 100 Selection.Interior.ColorIndex = 4 Case Is = 90 Selection.Interior.ColorIndex = 35 Case Is = 80 Selection.Interior.ColorIndex = 36 Case Is = 70 Selection.Interior.ColorIndex = 7 Case Is = 1 Selection.Interior.ColorIndex = 54 Case Else Selection.Interior.ColorIndex = 3 End Select ' This colors the blank empty cells Else cell.Select Selection.Interior.ColorIndex = 3 End If End If Next cell End Sub |
colorindex = xlnone on condition of month of year
Jane,
For Each MyCell In Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is = 4 MyCell.Interior.ColorIndex = xlNone Case Is = 35 MyCell.Interior.ColorIndex = xlNone Case Is = 36 MyCell.Interior.ColorIndex = xlNone Case Is = 7 MyCell.Interior.ColorIndex = xlNone Case Is = 54 MyCell.Interior.ColorIndex = xlNone End Select Next MyCell Sorry, I don't know the CI for light purple. You could step through (F8) your code until MyCell points to a light purple cell. Then hover your cursor over MyCell.Interior.ColorIndex. A tooltip with the value will pop up. Henry "Jane" wrote in message ... Thanks so much for your reply. It didn't work completely. It didn't work on cells with background of red and there was one cell that is light purple that didn't change. Do I have the wrong color code for light purple? This is what I added. Did I do it right? For Each MyCell In Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is = 4 MyCell.Interior.ColorIndex = xlNone End Select Next For Each MyCell In Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is = 35 MyCell.Interior.ColorIndex = xlNone End Select Next For Each MyCell In Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is = 36 MyCell.Interior.ColorIndex = xlNone End Select Next For Each MyCell In Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is = 7 MyCell.Interior.ColorIndex = xlNone End Select Next For Each MyCell In Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is = 54 MyCell.Interior.ColorIndex = xlNone End Select Next "Henry" wrote: Try again! Correct this time. Dim MyCell As Range For Each MyCell in Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is =4 MyCell.Interior.ColorIndex = xlNone Etc End Select Next MyCell Henry "Henry" wrote in message ... Jane, Something like "Jane" wrote in message ... Please see my code below. What is happening here is when the month changes to Aug for example certain background colors which were colored in Jul will now be colored in Aug. Everything works except Now that we are in the month of Aug, Jul background colors are still colored and I now do not want those cells to have color anymore. The only coloring should be in current month, mmm. Can you help fix my code. Very new to this. Thank you. ' ALSO, HOW DO I MAKE THIS CODE ACTIVATE WHEN I OPEN THE WORKBOOK? 'HOW DO I CHANGE THIS CODE SO IT WORKS FOR SPECIFIC SHEETS (I.E. SHEET1, SHEET2, ETC.) BUT NOT ALL SHEETS 'WHEN I OPEN THE WORKBOOK? Sub CFormat() Dim rng As Range, cell As Range Dim ncol As Integer, lrow As Long Dim pcnt As Double, divisor As Double 'Sheet1 is sheet name ThisWorkbook.Worksheets("Sheet1").Activate ' Find column for current Month (add 5 to start in colum F onwards) ' Define "CurMonth" and "HdrMonths" in spreadsheet ncol = Application.WorksheetFunction.match(Range("a3"), Range("F3:Q3"), 0) + 5 ' Find last row of data in current month column lrow = Cells(Rows.Count, 1).End(xlUp).Row Range("F9:Q500").Select 'Clears the colours 'Selection.Interior.ColorIndex = xlNone 'THIS STATEMENT DELETES ALL CELL BACKGROUND COLOR WHICH 'IS NOT WHAT I NEED...I NEED SOMETHING LIKE THE FOLLOWING.. Select Case Case Is = Interior.ColorIndex = 4 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 35 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 36 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 7 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 54 Selection.Interior.ColorIndex = xlNone End Select ' Set range to cells for current month (WHICH IS CELL A3) starting row 20 Set rng = Range(Cells(20, ncol), Cells(lrow, ncol)) ' Set Divisor for current month divisor = Cells(5, ncol) ' Loop through all cells in range For Each cell In rng ' Check length of cell in column A If Len(cell.Offset(0, -(ncol - 1))) = 4 Then ' Calculate perecentage If Application.IsNumber(cell) Then ' Is this cell a number ? ' Calculate percentage pcnt = (cell / divisor) * 100 cell.Select ' Set colorindex based on percentage Select Case pcnt Case Is 100 Selection.Interior.ColorIndex = 4 Case Is = 90 Selection.Interior.ColorIndex = 35 Case Is = 80 Selection.Interior.ColorIndex = 36 Case Is = 70 Selection.Interior.ColorIndex = 7 Case Is = 1 Selection.Interior.ColorIndex = 54 Case Else Selection.Interior.ColorIndex = 3 End Select ' This colors the blank empty cells Else cell.Select Selection.Interior.ColorIndex = 3 End If End If Next cell End Sub |
colorindex = xlnone on condition of month of year
Thanks so very much. All is great! Hope you have a great week ahead.
"Henry" wrote: Jane, For Each MyCell In Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is = 4 MyCell.Interior.ColorIndex = xlNone Case Is = 35 MyCell.Interior.ColorIndex = xlNone Case Is = 36 MyCell.Interior.ColorIndex = xlNone Case Is = 7 MyCell.Interior.ColorIndex = xlNone Case Is = 54 MyCell.Interior.ColorIndex = xlNone End Select Next MyCell Sorry, I don't know the CI for light purple. You could step through (F8) your code until MyCell points to a light purple cell. Then hover your cursor over MyCell.Interior.ColorIndex. A tooltip with the value will pop up. Henry "Jane" wrote in message ... Thanks so much for your reply. It didn't work completely. It didn't work on cells with background of red and there was one cell that is light purple that didn't change. Do I have the wrong color code for light purple? This is what I added. Did I do it right? For Each MyCell In Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is = 4 MyCell.Interior.ColorIndex = xlNone End Select Next For Each MyCell In Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is = 35 MyCell.Interior.ColorIndex = xlNone End Select Next For Each MyCell In Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is = 36 MyCell.Interior.ColorIndex = xlNone End Select Next For Each MyCell In Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is = 7 MyCell.Interior.ColorIndex = xlNone End Select Next For Each MyCell In Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is = 54 MyCell.Interior.ColorIndex = xlNone End Select Next "Henry" wrote: Try again! Correct this time. Dim MyCell As Range For Each MyCell in Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is =4 MyCell.Interior.ColorIndex = xlNone Etc End Select Next MyCell Henry "Henry" wrote in message ... Jane, Something like "Jane" wrote in message ... Please see my code below. What is happening here is when the month changes to Aug for example certain background colors which were colored in Jul will now be colored in Aug. Everything works except Now that we are in the month of Aug, Jul background colors are still colored and I now do not want those cells to have color anymore. The only coloring should be in current month, mmm. Can you help fix my code. Very new to this. Thank you. ' ALSO, HOW DO I MAKE THIS CODE ACTIVATE WHEN I OPEN THE WORKBOOK? 'HOW DO I CHANGE THIS CODE SO IT WORKS FOR SPECIFIC SHEETS (I.E. SHEET1, SHEET2, ETC.) BUT NOT ALL SHEETS 'WHEN I OPEN THE WORKBOOK? Sub CFormat() Dim rng As Range, cell As Range Dim ncol As Integer, lrow As Long Dim pcnt As Double, divisor As Double 'Sheet1 is sheet name ThisWorkbook.Worksheets("Sheet1").Activate ' Find column for current Month (add 5 to start in colum F onwards) ' Define "CurMonth" and "HdrMonths" in spreadsheet ncol = Application.WorksheetFunction.match(Range("a3"), Range("F3:Q3"), 0) + 5 ' Find last row of data in current month column lrow = Cells(Rows.Count, 1).End(xlUp).Row Range("F9:Q500").Select 'Clears the colours 'Selection.Interior.ColorIndex = xlNone 'THIS STATEMENT DELETES ALL CELL BACKGROUND COLOR WHICH 'IS NOT WHAT I NEED...I NEED SOMETHING LIKE THE FOLLOWING.. Select Case Case Is = Interior.ColorIndex = 4 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 35 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 36 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 7 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 54 Selection.Interior.ColorIndex = xlNone End Select ' Set range to cells for current month (WHICH IS CELL A3) starting row 20 Set rng = Range(Cells(20, ncol), Cells(lrow, ncol)) ' Set Divisor for current month divisor = Cells(5, ncol) ' Loop through all cells in range For Each cell In rng ' Check length of cell in column A If Len(cell.Offset(0, -(ncol - 1))) = 4 Then ' Calculate perecentage If Application.IsNumber(cell) Then ' Is this cell a number ? ' Calculate percentage pcnt = (cell / divisor) * 100 cell.Select ' Set colorindex based on percentage Select Case pcnt Case Is 100 Selection.Interior.ColorIndex = 4 Case Is = 90 Selection.Interior.ColorIndex = 35 Case Is = 80 Selection.Interior.ColorIndex = 36 Case Is = 70 Selection.Interior.ColorIndex = 7 Case Is = 1 Selection.Interior.ColorIndex = 54 Case Else Selection.Interior.ColorIndex = 3 End Select ' This colors the blank empty cells Else cell.Select Selection.Interior.ColorIndex = 3 End If End If Next cell End Sub |
colorindex = xlnone on condition of month of year
Jane,
To refer to the month before last, use: MyMonth =Month(Date) -2 This will give you the month number (1 to 12) If you want to refer to a particular column (assuming the months are in columns A to L) Range("A1").Offset(0,MyMonth -1) will refer to the cell with the Monthname in it. If we take today's date Month(Date) = 8 'August Month(Date) - 2 = 6 'June MyMonth = 6 Range("A1").Offset(0,MyMonth -1) = Goto A1 and then move 6 - 1 = 5 columns to the right = Cell F1 which will contain the text "June" If you prefer, you can put this all into 1 line: Range("A1").Offset(0,Month(Date) -3) to give the same result without the intermediate variable (MyMonth) This will also run faster. Henry "Jane" wrote in message ... Henry, I fixed my problem. It was on my side. What you gave me was great. I realize I have another problem though which is, if we are in the month of "Aug" I am inputting month end information for "Jul" so month end "Jun" needs to have the background color change to "no color". Your help is appreciated. "Henry" wrote: Try again! Correct this time. Dim MyCell As Range For Each MyCell in Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is =4 MyCell.Interior.ColorIndex = xlNone Etc End Select Next MyCell Henry "Henry" wrote in message ... Jane, Something like "Jane" wrote in message ... Please see my code below. What is happening here is when the month changes to Aug for example certain background colors which were colored in Jul will now be colored in Aug. Everything works except Now that we are in the month of Aug, Jul background colors are still colored and I now do not want those cells to have color anymore. The only coloring should be in current month, mmm. Can you help fix my code. Very new to this. Thank you. ' ALSO, HOW DO I MAKE THIS CODE ACTIVATE WHEN I OPEN THE WORKBOOK? 'HOW DO I CHANGE THIS CODE SO IT WORKS FOR SPECIFIC SHEETS (I.E. SHEET1, SHEET2, ETC.) BUT NOT ALL SHEETS 'WHEN I OPEN THE WORKBOOK? Sub CFormat() Dim rng As Range, cell As Range Dim ncol As Integer, lrow As Long Dim pcnt As Double, divisor As Double 'Sheet1 is sheet name ThisWorkbook.Worksheets("Sheet1").Activate ' Find column for current Month (add 5 to start in colum F onwards) ' Define "CurMonth" and "HdrMonths" in spreadsheet ncol = Application.WorksheetFunction.match(Range("a3"), Range("F3:Q3"), 0) + 5 ' Find last row of data in current month column lrow = Cells(Rows.Count, 1).End(xlUp).Row Range("F9:Q500").Select 'Clears the colours 'Selection.Interior.ColorIndex = xlNone 'THIS STATEMENT DELETES ALL CELL BACKGROUND COLOR WHICH 'IS NOT WHAT I NEED...I NEED SOMETHING LIKE THE FOLLOWING.. Select Case Case Is = Interior.ColorIndex = 4 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 35 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 36 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 7 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 54 Selection.Interior.ColorIndex = xlNone End Select ' Set range to cells for current month (WHICH IS CELL A3) starting row 20 Set rng = Range(Cells(20, ncol), Cells(lrow, ncol)) ' Set Divisor for current month divisor = Cells(5, ncol) ' Loop through all cells in range For Each cell In rng ' Check length of cell in column A If Len(cell.Offset(0, -(ncol - 1))) = 4 Then ' Calculate perecentage If Application.IsNumber(cell) Then ' Is this cell a number ? ' Calculate percentage pcnt = (cell / divisor) * 100 cell.Select ' Set colorindex based on percentage Select Case pcnt Case Is 100 Selection.Interior.ColorIndex = 4 Case Is = 90 Selection.Interior.ColorIndex = 35 Case Is = 80 Selection.Interior.ColorIndex = 36 Case Is = 70 Selection.Interior.ColorIndex = 7 Case Is = 1 Selection.Interior.ColorIndex = 54 Case Else Selection.Interior.ColorIndex = 3 End Select ' This colors the blank empty cells Else cell.Select Selection.Interior.ColorIndex = 3 End If End If Next cell End Sub |
colorindex = xlnone on condition of month of year
I think that will work. Thanks so much!
"Henry" wrote: Jane, To refer to the month before last, use: MyMonth =Month(Date) -2 This will give you the month number (1 to 12) If you want to refer to a particular column (assuming the months are in columns A to L) Range("A1").Offset(0,MyMonth -1) will refer to the cell with the Monthname in it. If we take today's date Month(Date) = 8 'August Month(Date) - 2 = 6 'June MyMonth = 6 Range("A1").Offset(0,MyMonth -1) = Goto A1 and then move 6 - 1 = 5 columns to the right = Cell F1 which will contain the text "June" If you prefer, you can put this all into 1 line: Range("A1").Offset(0,Month(Date) -3) to give the same result without the intermediate variable (MyMonth) This will also run faster. Henry "Jane" wrote in message ... Henry, I fixed my problem. It was on my side. What you gave me was great. I realize I have another problem though which is, if we are in the month of "Aug" I am inputting month end information for "Jul" so month end "Jun" needs to have the background color change to "no color". Your help is appreciated. "Henry" wrote: Try again! Correct this time. Dim MyCell As Range For Each MyCell in Range("F9:Q500") Select Case MyCell.Interior.ColorIndex Case Is =4 MyCell.Interior.ColorIndex = xlNone Etc End Select Next MyCell Henry "Henry" wrote in message ... Jane, Something like "Jane" wrote in message ... Please see my code below. What is happening here is when the month changes to Aug for example certain background colors which were colored in Jul will now be colored in Aug. Everything works except Now that we are in the month of Aug, Jul background colors are still colored and I now do not want those cells to have color anymore. The only coloring should be in current month, mmm. Can you help fix my code. Very new to this. Thank you. ' ALSO, HOW DO I MAKE THIS CODE ACTIVATE WHEN I OPEN THE WORKBOOK? 'HOW DO I CHANGE THIS CODE SO IT WORKS FOR SPECIFIC SHEETS (I.E. SHEET1, SHEET2, ETC.) BUT NOT ALL SHEETS 'WHEN I OPEN THE WORKBOOK? Sub CFormat() Dim rng As Range, cell As Range Dim ncol As Integer, lrow As Long Dim pcnt As Double, divisor As Double 'Sheet1 is sheet name ThisWorkbook.Worksheets("Sheet1").Activate ' Find column for current Month (add 5 to start in colum F onwards) ' Define "CurMonth" and "HdrMonths" in spreadsheet ncol = Application.WorksheetFunction.match(Range("a3"), Range("F3:Q3"), 0) + 5 ' Find last row of data in current month column lrow = Cells(Rows.Count, 1).End(xlUp).Row Range("F9:Q500").Select 'Clears the colours 'Selection.Interior.ColorIndex = xlNone 'THIS STATEMENT DELETES ALL CELL BACKGROUND COLOR WHICH 'IS NOT WHAT I NEED...I NEED SOMETHING LIKE THE FOLLOWING.. Select Case Case Is = Interior.ColorIndex = 4 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 35 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 36 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 7 Selection.Interior.ColorIndex = xlNone Case Is = Interior.ColorIndex = 54 Selection.Interior.ColorIndex = xlNone End Select ' Set range to cells for current month (WHICH IS CELL A3) starting row 20 Set rng = Range(Cells(20, ncol), Cells(lrow, ncol)) ' Set Divisor for current month divisor = Cells(5, ncol) ' Loop through all cells in range For Each cell In rng ' Check length of cell in column A If Len(cell.Offset(0, -(ncol - 1))) = 4 Then ' Calculate perecentage If Application.IsNumber(cell) Then ' Is this cell a number ? ' Calculate percentage pcnt = (cell / divisor) * 100 cell.Select ' Set colorindex based on percentage Select Case pcnt Case Is 100 Selection.Interior.ColorIndex = 4 Case Is = 90 Selection.Interior.ColorIndex = 35 Case Is = 80 Selection.Interior.ColorIndex = 36 Case Is = 70 Selection.Interior.ColorIndex = 7 Case Is = 1 Selection.Interior.ColorIndex = 54 Case Else Selection.Interior.ColorIndex = 3 End Select ' This colors the blank empty cells Else cell.Select Selection.Interior.ColorIndex = 3 End If End If Next cell End Sub |
| All times are GMT +1. The time now is 03:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com