Change interior colour of selected cells

[danbates]

Hi,

Please can someone help me change the colour of the selected cells from this line of code:

ActiveSheet.Range(Cells(ActiveCell.Row, 2), Cells(ActiveCell.Row, 14)).Select

I'd like them to be red please.

Thanks

Dan

 

[My Aswer Is This]

Try this:

ActiveSheet.Range(Cells(ActiveCell.Row, 2), Cells(ActiveCell.Row, 14)).Interior.Color = vbRed

 

[danbates]

Hi,

it's giving me the following error: Run time error 1004 - Application - defined or object defined error.

Thanks again

Dan

 

[My Aswer Is This]

Well you will notice all I did was remove select and add interior.color=VbRed

This works for me.

Show me what other code you have in your script.

Maybe the other part of your script requires select.
Try this:

ActiveSheet.Range(Cells(ActiveCell.Row, 2), Cells(ActiveCell.Row, 14)).Select
Selection.Interior.Color = vbRed

 

[danbates]

Hi,

That also returns the same error.

Here is my full code:

Option Explicit

Sub BlankOutDAILYCIL()
    
    Dim nLastRowA As Long
    Dim nNextRowAP As Long
    Dim Myrange As Range
    Dim Ans As Integer
    Dim iRow As Long
    
     ActiveSheet.Range(Cells(ActiveCell.Row, 2), Cells(ActiveCell.Row, 14)).Interior.Color = vbRed
    'ActiveSheet.Range(Cells(ActiveCell.Row, 2), Cells(ActiveCell.Row, 14)).Select


    Ans = MsgBox("*************** WARNING ***************" & vbLf & "Are you sure you want to blank out:" & vbLf & _
    "Row " & ActiveCell.Row & vbLf & Cells(ActiveCell.Row, "B").Value & "-" & Cells(ActiveCell.Row, "A").Text & Cells(ActiveCell.Row - 1, "A").Text, vbYesNo, "Rychiger Operator")
    
    ''If yes then proceed
    If Not Ans = vbYes Then Exit Sub
    
        With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    
    With Worksheets("DAILY_CIL")
    
        'Exit if the row is NOT a data line
'    If iRow <= 6 Then
'        Exit Sub
'    End If


        'Disable Excel Events
    Application.EnableEvents = False
  
    'Unprotect the Sheet
    Call UnprotectTheActiveSheet
        
        nLastRowA = .Cells(Rows.Count, "D").End(xlUp).Row + 1
        nNextRowAP = .Cells(Rows.Count, "D").End(xlUp).Row + 1
   
        .Range(.Cells(nNextRowAP, "L"), .Cells(nLastRowA, "L")).Select
        
        ActiveCell.Offset(0, 0) = "X"
                                   
        .Range(.Cells(nNextRowAP, "J"), .Cells(nLastRowA, "K")).Select


    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    With Selection.Font
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
    End With
    
    ActiveCell.Offset(0, 0) = "NOT FILLED OUT BY PREVIOUS SHIFT!"
    
    Selection.Font.Underline = xlUnderlineStyleNone
    
    .Range(.Cells(nNextRowAP, "I"), .Cells(nLastRowA, "I")).Select
    Selection.FormatConditions.Delete
    
    .Range(.Cells(nNextRowAP, "C"), .Cells(nLastRowA, "C")).Select
    
    ActiveCell.Offset(0, 0) = "N/A"
    ActiveCell.Offset(0, 1) = "N/A"
    ActiveCell.Offset(0, 2) = "N/A"
    ActiveCell.Offset(0, 3) = "N/A"
    ActiveCell.Offset(0, 4) = "N/A"
    ActiveCell.Offset(0, 5) = "N/A"
    ActiveCell.Offset(0, 6) = "N/A"
    ActiveCell.Offset(0, 9) = "N/A"
    ActiveCell.Offset(0, 10) = "N/A"
    ActiveCell.Offset(0, 11) = "N/A"
        
Call FindNextRowDAILYCIL


    'Protect the Sheet again
    Call ProtectTheActiveSheet
            
    'Enable Excel Events
    Application.EnableEvents = True


'End If
End With


    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
End With
End With
End Sub

Thanks again
Dan

 

[My Aswer Is This]

It may be because you have a protected sheet. And you unprotect the sheet after this line of code.
If your sheet is protected unprotect it at the beginning of the script and reprotect it at end of script.

Try the simple line of code by itself on a unprotected sheet.

 

[danbates]

Hi,

Yes that was the problem, thank you for your help.

Kind Regards

Dan

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Hi,

Yes that was the problem, thank you for your help.

Kind Regards

Dan