CODEWITHSUNDEEP
vbaexcel
Splendonia
Splendonia

Reputation: 1369

Change color of entire row before or after inserting values on a sheet VBA

I'm Trying to change the value of a row when a determine condition happens, and I was thinking of doing it after the insert was made, but maybe it's easier before the insert (?) The thing is i've tried both, and i'm doing it wrong because it's not working

Code:

With ThisWorkbook.Worksheets("Site Configuration List")
       ' Tried this .Range("A").EntireRow.Interior.Color = 49407
        .Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Code
        .Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Name
        .Range("C" & Rows.Count).End(xlUp).Offset(1).Value = "" & Contract & ""
        .Range("D" & Rows.Count).End(xlUp).Offset(1).Value = SiteCode
        .Range("E" & Rows.Count).End(xlUp).Offset(1).Value = SiteName
        .Range("G" & Rows.Count).End(xlUp).Offset(1).Value = Approver
        .Range("K" & Rows.Count).End(xlUp).Offset(1).Value = ItemCode
        .Range("M" & Rows.Count).End(xlUp).Offset(1).Value = RequiredQty
        .Range("N" & Rows.Count).End(xlUp).Offset(1).Value = ControlFlag
    If Color = 0 Then
       ', this .Offset(-1).Interior.Color = 49407
        ',this .Interior.Color = 49407
     'and this .EntireRow.Interior.Color = 255
    Else


    End If
    End With

Obviously not at the same time, but no of those work. What am I doing wrong and how can I fix it? I'm new to VBA and some of the simplest things usually take me more time to figure out. What I've used in order to try to achieve what I want I've taken it from other similar questions. The call to the sub where the code above is implemented, is inside a loop, however, with each one of the things I've tried, when it gets to the line the program just stops. No error or anything.

Upvotes: 0

Views: 9936

Answers (2)

Jaycal
Jaycal

Reputation: 2087

The range was not declared correctly. instead of 

.Range("A").EntireRow.Interior.Color = 49407

it needs to be 

.Range("1:1").EntireRow.Interior.Color = 49407

if you want to hightlight the entire row or 

.Range("A:A").EntireColumn.Interior.Color = 49407

to highlight the entire column.

EDIT

To color the last row of data, you'll need to use the Rows method, along with the number of the last row that you get from your Rows.Count. Code would look like the following 

Rows(Range("A" & Rows.Count).End(xlUp).Row).EntireRow.Interior.Color = 49407

Or

Rows(Range("A" & Rows.Count).End(xlUp).offset(1).Row) _
                                           .EntireRow.Interior.Color = 49407

Upvotes: 2

DaveU
DaveU

Reputation: 1082

Changing your code as follows should do the trick.

With ThisWorkbook.Worksheets("Site Configuration List")
    .Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.Interior.Color = 49407
    .Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Code
    .Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Name

Upvotes: 2

Related Questions