.Interior.ColorIndex with Conditions

[Kel09]

Hi All,

I need to add to this code .Interior.ColorIndex where certain conditions within a range apply. I would like to place it within the With statement(Highlighted red).

The code works perfectly otherwise.

Anyone have any ideas.

Many thanks in Advance.

My code:

Dim MACRO As Workbook
Dim Timestamp As Date
Dim Lastrow As Integer
Dim ii As Long
Dim i As Integer
Dim erow As Integer
Dim Month As String
Dim finalRow As Integer
Month = "July"
Timestamp = Now()
Lastrow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).row
For i = 1 To Lastrow
Set MACRO = ActiveWorkbook
row = 2


'Application.ScreenUpdating = False
If Cells(i, 1) = 1 Then
MACRO.Worksheets("Data").Range(Cells(i, 3), Cells(i, 23)).Select
Selection.Copy
Workbooks.Open Filename:="C:\" 'My File Location
Worksheets(Month).Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 1).row
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.PasteSpecial
With ActiveSheet.Range("B1:U1").End(xlDown) ' Ctrl+Down
.Offset(1, -1).Range("B1:U1").Select '
.Offset(1, -1).Range("A1:U1").Interior.ColorIndex = 24
.Offset(1, -1).Range("A1:U1").Font.Bold = True
Application.CommandBars.ExecuteMso ("AutoSum")
.Cells.End(xlDown).Range("A") = "Total" ' This part doesn’t work!
ActiveSheet.Cells.End(xlUp).Offset(0, 23) = "as @ " & Now()
.Offset(0, 22).Cells.End(xlUp).Interior.ColorIndex = 28
End With
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False


End If


Next i


End Sub

 

[Andrew Poulsom]

Is this what you want?

    With ActiveSheet.Range("B1:U1")
        With .End(xlDown).Offset(1, -1) ' Ctrl+Down
            .Range("B1:U1").Select '
            .Range("A1:U1").Interior.ColorIndex = 24
            .Range("A1:U1").Font.Bold = True
            Application.CommandBars.ExecuteMso ("AutoSum")
            .Range("A1").Value = "Total" ' This part doesn’t work!
        End With
        With .Offset(0, 20).Cells(1, 1)
            .Value = "as @ " & Now()
            .Interior.ColorIndex = 28
        End With
    End With

 

[mrmmickle1]

Change this line:

[COLOR=#ff0000].Cells.End(xlDown).Range("A") = "Total" [/COLOR][COLOR=#008000]' This part doesn’t work![/COLOR]

To This:

.Cells.End(xlDown).Offset(, -1) = "Total"

Not sure this is the best way to go about it but that should get what you want...

 

[Kel09]

Change this line:

[COLOR=#ff0000].Cells.End(xlDown).Range("A") = "Total" [/COLOR][COLOR=#008000]' This part doesn’t work![/COLOR]

To This:

.Cells.End(xlDown).Offset(, -1) = "Total"

Not sure this is the best way to go about it but that should get what you want...

Works perfectly thank you very much!

Any ideas to my initial problem I have had a go since my last post and have come up with something but it only satisfies the first condition. Relevant snippet of the code shown below. (Basically what I am trying to say is 'if Small color cell range red, if large color cell range green.)

Dim S As Range
Dim L As Range

With ActiveSheet.Range("A14:H14").End(xlDown)
Set S = Cells.Find("Small", MatchCase:=False, lookat:=xlWhole, LookIn:=xlValues)
Set L = Cells.Find("Large", MatchCase:=False, lookat:=xlWhole, LookIn:=xlValues)
If Not S Is Nothing Then
ActiveSheet.Range(Cells(14, 1), Cells(14, 8)).Interior.ColorIndex = 3
Else
If S Is Nothing Then
ActiveSheet.Range(Cells(14, 1), Cells(14, 8)).Interior.ColorIndex = 4
Else
If L Is Nothing Then
ActiveSheet.Range(Cells(14, 1), Cells(14, 8)).Interior.ColorIndex = 4
End If
End If
End If
End With

 

[Kel09]

See below.

 

[Kel09]

Is this what you want?

    With ActiveSheet.Range("B1:U1")
        With .End(xlDown).Offset(1, -1) ' Ctrl+Down
            .Range("B1:U1").Select '
            .Range("A1:U1").Interior.ColorIndex = 24
            .Range("A1:U1").Font.Bold = True
            Application.CommandBars.ExecuteMso ("AutoSum")
            .Range("A1").Value = "Total" ' This part doesn’t work!
        End With
        With .Offset(0, 20).Cells(1, 1)
            .Value = "as @ " & Now()
            .Interior.ColorIndex = 28
        End With
    End With

Apologies, I don't think I was very clear in my first post!

I wanted to build on my initial code to add something like say: 'if Small color cell range red, if large color cell range green. I have had a go since my last post and have come up with something but it only satisfies the first condition. Relevant snippet of the code shown below.

Dim S As Range
Dim L As Range

With ActiveSheet.Range("A14:H14").End(xlDown)
Set S = Cells.Find("Small", MatchCase:=False, lookat:=xlWhole, LookIn:=xlValues)
Set L = Cells.Find("Large", MatchCase:=False, lookat:=xlWhole, LookIn:=xlValues)
If Not S Is Nothing Then
ActiveSheet.Range(Cells(14, 1), Cells(14, 8)).Interior.ColorIndex = 3
Else
If S Is Nothing Then
ActiveSheet.Range(Cells(14, 1), Cells(14, 8)).Interior.ColorIndex = 4
Else
If L Is Nothing Then
ActiveSheet.Range(Cells(14, 1), Cells(14, 8)).Interior.ColorIndex = 4
End If
End If
End If
End With

 

[Andrew Poulsom]

I don't follow any of that sorry. It doesn't seem to relate in any way to your original code.

 

[mrmmickle1]

I think this is what your trying to do:

[COLOR=#0000ff]Sub[/COLOR] HighlightRowCriteria()

  [COLOR=#0000ff]  Dim [/COLOR]S[COLOR=#0000ff] As[/COLOR] Range
[COLOR=#0000ff]    Dim [/COLOR]L[COLOR=#0000ff] As [/COLOR]Range
    
  [COLOR=#0000ff]  With [/COLOR]ActiveSheet.Range("A14:H14").End(xlDown).Select
    
   [COLOR=#0000ff] Set[/COLOR] S = Cells.Find("Small", MatchCase:=False, lookat:=xlWhole, LookIn:=xlValues)
    [COLOR=#0000ff]Set[/COLOR] L = Cells.Find("Large", MatchCase:=False, lookat:=xlWhole, LookIn:=xlValues)
    
     [COLOR=#0000ff]   If Not[/COLOR] S[COLOR=#0000ff] Is Nothing Then[/COLOR]
            ActiveSheet.Range(Cells(14, 1), Cells(14, 8)).Interior.ColorIndex = 3
       [COLOR=#0000ff] ElseIf [/COLOR]S [COLOR=#0000ff]Is Nothing Then[/COLOR]
            ActiveSheet.Range(Cells(14, 1), Cells(14, 8)).Interior.ColorIndex = 4
[COLOR=#0000ff]        Else[/COLOR]
            ActiveSheet.Range(Cells(14, 1), Cells(14, 8)).Interior.ColorIndex = 4
[COLOR=#0000ff]        End If[/COLOR]

[COLOR=#0000ff]    End With[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]

I don't see how it relates to your original question though.

 

[Kel09]

I think this is what your trying to do:

[COLOR=#0000ff]Sub[/COLOR] HighlightRowCriteria()

  [COLOR=#0000ff]  Dim [/COLOR]S[COLOR=#0000ff] As[/COLOR] Range
[COLOR=#0000ff]    Dim [/COLOR]L[COLOR=#0000ff] As [/COLOR]Range
    
  [COLOR=#0000ff]  With [/COLOR]ActiveSheet.Range("A14:H14").End(xlDown).Select
    
   [COLOR=#0000ff] Set[/COLOR] S = Cells.Find("Small", MatchCase:=False, lookat:=xlWhole, LookIn:=xlValues)
    [COLOR=#0000ff]Set[/COLOR] L = Cells.Find("Large", MatchCase:=False, lookat:=xlWhole, LookIn:=xlValues)
    
     [COLOR=#0000ff]   If Not[/COLOR] S[COLOR=#0000ff] Is Nothing Then[/COLOR]
            ActiveSheet.Range(Cells(14, 1), Cells(14, 8)).Interior.ColorIndex = 3
       [COLOR=#0000ff] ElseIf [/COLOR]S [COLOR=#0000ff]Is Nothing Then[/COLOR]
            ActiveSheet.Range(Cells(14, 1), Cells(14, 8)).Interior.ColorIndex = 4
[COLOR=#0000ff]        Else[/COLOR]
            ActiveSheet.Range(Cells(14, 1), Cells(14, 8)).Interior.ColorIndex = 4
[COLOR=#0000ff]        End If[/COLOR]

[COLOR=#0000ff]    End With[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]

I don't see how it relates to your original question though.

Many thanks for responding. Apologies, my original quote was not entirely focused. I have, however found the solution to my problem:

Dim LastRow As Long

LastRow = Cells.Find("*", , LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True).Row
With ActiveSheet.Range("A13:H50")
If Not .Cells.Find("Small") Is Nothing Then
Cells(LastRow, 1).Resize(, 8).Interior.ColorIndex = 3
If Not .Cells.Find("Large") Is Nothing Then
Cells(LastRow, 1).Resize(, 8).Interior.ColorIndex = 4
End If
End If
End With

 

[mrmmickle1]

Glad you were able to solve your issue. Thanks for posting the solution.

This might work too (It gets rid of an End If) ??:

[COLOR=#0000ff]Sub [/COLOR]Test()
    
[COLOR=#0000ff]Dim [/COLOR]LastRow [COLOR=#0000ff]As Long[/COLOR]

LastRow = Cells.Find("*", , LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True).Row

    [COLOR=#0000ff]With[/COLOR] ActiveSheet.Range("A13:H50")
    
        [COLOR=#0000ff]If Not[/COLOR] .Cells.Find("Small")[COLOR=#0000ff] Is Nothing Then[/COLOR]
            Cells(LastRow, 1).Resize(, 8).Interior.ColorIndex = 3
        [COLOR=#0000ff]ElseIf Not [/COLOR].Cells.Find("Large") [COLOR=#0000ff]Is Nothing Then[/COLOR]
            Cells(LastRow, 1).Resize(, 8).Interior.ColorIndex = 4
[COLOR=#0000ff]        End If[/COLOR]

[COLOR=#0000ff]    End With[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]