Re: 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" <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
> >
> >
>
>
>
.