colorindex = xlnone on condition of month of year

J

Jane

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
 
H

Henry

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
 
H

Henry

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
 
J

Jane

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
 
J

Jane

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.
 
H

Henry

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
 
H

Henry

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
 
J

Jane

I think that will work. Thanks so much!

Henry said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error 13 6
VBA Count most current 6 months 0
Multiple OR and AND conditions 1
Macro Continuous Execution 8
Chart error: plotting series data XY scatter plot 3
"ElseIf c.Value = "X_Clear" Then" and "...Target.Cells.Count > 1"don't work 4
More than 4 conditional formating? 14
Search for Values part2 2

Top