ColorIndex ...

[jlarkin]

nar... nar...

I have a new more intense problem.

I've been running so much damn code that it is now an invali
procedure.

Granted, my macro is very sloppy and probably can be cut down.

put it this way...

I have this

'checking row 8 for Susan'
If Sheets("Model Calculations").Range("C8") = "Susan" And Sheets("Mode
Calculations").Range("C8").Interior.ColorIndex = 34 Or Sheets("Mode
Calculations").Range("d8") = "Susan" And Sheets("Mode
Calculations").Range("d8").Interior.ColorIndex = 34 Or Sheets("Mode
Calculations").Range("e8") = "Susan" And Sheets("Mode
Calculations").Range("e8").Interior.ColorIndex = 34 Or Sheets("Mode
Calculations").Range("f8") = "Susan" And Sheets("Mode
Calculations").Range("f8").Interior.ColorIndex = 34 Or Sheets("Mode
Calculations").Range("g8") = "Susan" And Sheets("Mode
Calculations").Range("g8").Interior.ColorIndex = 34 Or Sheets("Mode
Calculations").Range("h8") = "Susan" And Sheets("Mode
Calculations").Range("h8").Interior.ColorIndex = 34 Or Sheets("Mode
Calculations").Range("i8") = "Susan" And Sheets("Mode
Calculations").Range("i8").Interior.ColorIndex = 34 Or Sheets("Mode
Calculations").Range("j8") = "Susan" And Sheets("Mode
Calculations").Range("j8").Interior.ColorIndex = 34 Then
ActiveSheet.Range("b3").Select
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
Range("B3").Select
End If


I need it for a TON of rows... and more multiple individuals... no
just ole Susan...

So yeah... any alternative suggestions to get this macro working

 

[Myrna Larson]

Is this your actual code? It shouldn't compile like this. You are missing all
of the required leading dots inside the With/End With block.

Maybe the following will give you some ideas as to how to get it to work with
the additional rows and additional people, with less code.

I assumed there is one row to search for each person, not that you want to
search all rows for every person. Also, I'm not sure that B3 is the right cell
to color for everybody. You may need an array listing the cell reference for
each person.

If my assumptions aren't correct, please give the specifics: who are the
people, which rows are to be searched for each person, what cell is to be
colored if found.

Dim i As Variant
Dim N As Long
Dim NameList As Variant
Dim Rng As Range
Dim RowList As Variant
Dim WS As Worksheet

NameList = Array("Susan","Jane","Carol","Amy")
RowList = Array(8,9,10,11)

Set WS = Worksheets("Model Calculations")

For N = LBound(NameList) To UBound(NameList)
Set Rng = WS.Cells(RowList(N), 4).Resize(1, 7)
i = Application.MATCH(NameList(N), Rng, 0)
If IsNumeric(i) Then
If Rng.Cells(i).Interior.ColorIndex = 34 Then
With WS.Range("B3").Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
End If
End If
Next N