Re: 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" <henry.best1@xxxxxxxxxxxx> wrote in message
news:OhbJe.22471$Oe4.17499@xxxxxxxxxxxxxxxxxxxxxxx
> Jane,
>
> Something like
>
> >
>
>
> "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