VBA interior color change

[Enzo_Matrix]

I have a CF that applies to column G based on dates.
I want to take the color that is applied for overdue orders RGB(217,217,217) and apply it to the entire row instead of just the cells in column G.

This is what I've written, but it is not working the way I expect it should.

If Range("G:G").Interior.Color = RGB(217, 217, 217) Then      
     Range("A2:F100").Interior.Color = RGB(217, 217, 217)
End If

Please help.

 

[Scott T]

For cells colored by CF you need to use

Range("rangehere").DisplayFormat.Interior.Color

You do not need to use VBA as CF could highlight the row if you want.

 

[Norie]

Can't you extend the conditional formatting to apply to the whole row instead of only column G?

 

[Enzo_Matrix]

I did attempt to do that but for some reason it then takes the number formatting for the CF and applies it to the rest of the cells which changes my data.

 

[Scott T]

When using CF on multiple cells CF will change the cell reference just like if you entered it into the cell and copied it. When you put the formula in CF you will need to lock the references as need.

for example the formula would be written for A1 and column G is locked so it does not change

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1							fas		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
2							fas		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
3							fas		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
4							fas		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
5							fas		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
6							fas		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
7	This row is colored						7		TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
8							fas		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
9							fas		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
10							fas		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
11							fas		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
12							fas		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
13							fas		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
14							fas		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
15							fas		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula I1 =$G1=7 J1 =$G1=7 K1 =$G1=7 L1 =$G1=7 M1 =$G1=7 N1 =$G1=7 I2 =$G2=7 J2 =$G2=7 K2 =$G2=7 L2 =$G2=7 M2 =$G2=7 N2 =$G2=7 I3 =$G3=7 J3 =$G3=7 K3 =$G3=7 L3 =$G3=7 M3 =$G3=7 N3 =$G3=7 I4 =$G4=7 J4 =$G4=7 K4 =$G4=7 L4 =$G4=7 M4 =$G4=7 N4 =$G4=7 I5 =$G5=7 J5 =$G5=7 K5 =$G5=7 L5 =$G5=7 M5 =$G5=7 N5 =$G5=7 I6 =$G6=7 J6 =$G6=7 K6 =$G6=7 L6 =$G6=7 M6 =$G6=7 N6 =$G6=7 I7 =$G7=7 J7 =$G7=7 K7 =$G7=7 L7 =$G7=7 M7 =$G7=7 N7 =$G7=7 I8 =$G8=7 J8 =$G8=7 K8 =$G8=7 L8 =$G8=7 M8 =$G8=7 N8 =$G8=7 I9 =$G9=7 J9 =$G9=7 K9 =$G9=7 L9 =$G9=7 M9 =$G9=7 N9 =$G9=7 I10 =$G10=7 J10 =$G10=7 K10 =$G10=7 L10 =$G10=7 M10 =$G10=7 N10 =$G10=7 I11 =$G11=7 J11 =$G11=7 K11 =$G11=7 L11 =$G11=7 M11 =$G11=7 N11 =$G11=7 I12 =$G12=7 J12 =$G12=7 K12 =$G12=7 L12 =$G12=7 M12 =$G12=7 N12 =$G12=7 I13 =$G13=7 J13 =$G13=7 K13 =$G13=7 L13 =$G13=7 M13 =$G13=7 N13 =$G13=7 I14 =$G14=7 J14 =$G14=7 K14 =$G14=7 L14 =$G14=7 M14 =$G14=7 N14 =$G14=7 I15 =$G15=7 J15 =$G15=7 K15 =$G15=7 L15 =$G15=7 M15 =$G15=7 N15 =$G15=7 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

You can put the formula off to the side and copy it to see if it works as seen in I-N

 

[Enzo_Matrix]

I used this in hopes that it would work but not sure if I wrote it correctly.

    If Range("G:G") = ("" <= "" & Today()) Then
     Range("A:G").DisplayFormat.Interior.Color = RGB(217, 217, 217)
    End If

 

[Scott T]

If you want to use VBA instead of CF this should work.

Sub cf()
Dim lr As Long
lr = Cells(Rows.Count, "G").End(xlUp).Row
For Each cell In Range("G1:G" & lr)
    myrow = cell.Row
    If cell <= Date Then
        Range(Cells(myrow, "A"), Cells(myrow, "F")).Interior.Color = RGB(217, 217, 217)
    End If
Next cell
End Sub

DisplayFormat is used when seeing if a cell is colored by conditional formatting. You do not need to use when using VBA to color a cell.

 

[Enzo_Matrix]

It works perfectly.

One tweak i'm interested in making is starting the coloring at row 2 and working down instead of row 1. Is this an easy modification?

 

[Scott T]

Just change G1 to G2 in the for each cell line of code

Sub cf()
Dim lr As Long
lr = Cells(Rows.Count, "G").End(xlUp).Row
For Each cell In Range("G[COLOR=#FF0000]2[/COLOR]:G" & lr)
    myrow = cell.Row
    If cell <= Date Then
        Range(Cells(myrow, "A"), Cells(myrow, "F")).Interior.Color = RGB(217, 217, 217)
    End If
Next cell
End Sub

 

[Enzo_Matrix]

it's it's not one thing, it's another.

That sub works perfectly when I run it on it's own. However, when I run this sub, it does not color the proper cells anymore..

Sub Button_Update()' runs multiple macros as one button is clicked
    Call SortDate
    Call FilterCopy
    Call ProductionWeld
    Call ProductionComposite
    Call ProductionRubber
    
End Sub

All code for each of the last three subs are identical apart from name of sheets that change

Sub ProductionWeld()
'set fields of count as well as hose count for each category


Dim OD As Range
Set OD = Sheets("Weld").Range("B1")
Dim TDY As Range
Set TDY = Sheets("Weld").Range("D1")
Dim TOA As Range
Set TOA = Sheets("Weld").Range("F1")
Dim lr As Long
lr = Cells(Rows.Count, "G").End(xlUp).Row
    For Each Cell In Range("G2:G" & lr)
        myrow = Cell.Row
        
Sheets("Weld").Activate
    
    ActiveSheet.Range("A1").Select
        ActiveCell.Value = "Overdue"
    
        With OD
            .Formula = "=Sumif($G:$G,""<"" & Today(),$F:$F)"
            .Value = .Value
        End With


    ActiveSheet.Range("C1").Select
        ActiveCell.Value = "Today"
        
        With TDY
            .Formula = "=Sumif($G:$G,Today(),$F:$F)"
            .Value = .Value
        End With
    
    ActiveSheet.Range("E1").Select
        ActiveCell.Value = "Tomorrow or after"
                   
        With TOA
            .Formula = "=Sumif($G:$G,"">"" & Today(),$F:$F)"
            .Value = .Value
        End With
        
    ActiveSheet.Range("A1:F1").Select
        Selection.Font.Size = 20
        Selection.Font.Bold = True
        Selection.HorizontalAlignment = xlCenter
        Selection.VerticalAlignment = xlCenter
        Rows("1").RowHeight = 30
        Columns("A:H").AutoFit
        
            With Selection.Borders
                .Weight = xlMedium
                .LineStyle = xlContinuous
            End With
            
         If Cell <= Date Then
            Range(Cells(myrow, "A"), Cells(myrow, "G")).Interior.Color = RGB(217, 217, 217)
        End If
    Next Cell
        
End Sub