Re: 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" <Jane@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CA87224F-DCAB-4FC6-BFE8-8E22DE310A6D@xxxxxxxxxxxxxxxx
> 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 spread***
> 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


.

Quantcast