Highlight cells Data

[billandrew]

Good morning

I am trying to highlight cells with data in non contiguous columns. Below is the code I have started with, not sure what I am doing incorrectly.

Thank for any help.

Sub HighlightCellwithData2()    
    Dim i As Long, lr As Long
    
    lr = Range("H" & Rows.Count).End(xlUp).Row
    
    For i = 2 To lr
    
    If Range("A" & i) <> "" Then
    
    Range("A" & i).Interior.Color = RGB(218, 239, 245)
    
        If Range("H" & i) <> "" Then
    
    Range("H" & i).Interior.Color = RGB(218, 239, 245)
    
    End If
    
        End If
        
            
    
    Next
    
        
End Sub

 

[Peter_SSs]

Could we do it all at once with Conditional Formatting like this?

Sub HighlightData()
  Dim lr As Long
  
  lr = Range("H" & Rows.Count).End(xlUp).Row
  With Range("A2:A" & lr & " ,H2:H" & lr)
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=" & .Cells(1, 1).Address(0, 0) & "<>"""""
    .FormatConditions(1).Interior.Color = RGB(218, 239, 245)
  End With
End Sub

 

[billandrew]

Mr Peter Thank You. This works as requested.

If I may if there are additional columns needed how could I include them?

 

[Peter_SSs]

If I may if there are additional columns needed how could I include them?

The most efficient way depends on whether they are all disjoint, or perhaps some of them are joined, but the basic concept would be to add more sections like the coloured one below.

  With Range("A2:A" & lr & " ,H2:H" & lr)

 

[billandrew]

Struggling a bit to add Column C & Column G...

At the end of the above H2:H" & lr "add" & "

 

[billandrew]

Came up with another alternative, not sure if this is as efficient as it could be. The issue I believe I am having is the data in column A is not as long as Column H.

Maybe someone has a better idea...

[TABLE="width: 72"]
 <colgroup><col width="72" style="width:54pt"> </colgroup><tbody>[TR]
  [TD="class: xl63, width: 72"]    Dim Finalrow As Long, Finalrow1 As Long,  Finalrow2 As Long[/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    [/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    Finalrow = Cells(Rows.Count,  "A").End(xlUp).Row[/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    Finalrow1 = Cells(Rows.Count,  "D").End(xlUp).Row[/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    Finalrow2 = Cells(Rows.Count,  "H").End(xlUp).Row[/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    [/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    [/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    With Range("A2:A" &  Finalrow)[/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    [/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    .Interior.Color = RGB(218, 239, 245)[/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    [/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    End With[/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    [/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    [/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    With Range("D2:D" &  Finalrow1)[/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    [/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    .Interior.Color = RGB(217, 238, 208)[/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    [/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    End With[/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    [/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    With Range("H2:H" &  Finalrow2)[/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    [/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    .Interior.Color = RGB(255, 255, 204)[/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    [/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    End With[/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    [/TD]
 [/TR]
 [TR]
  [TD="class: xl63"]    End Sub[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Your code now appears to be colouring all cells in the column down to the last entry, whether they are blank or not. Previously, your code was only highlighting cells that actually contained data.
Has the requirement actually changed?

 

[billandrew]

No it has not. I am unable to add a third range. Thought this may be a solution.

Thank you.

 

[Peter_SSs]

No it has not.

Then it is as I said before, a case of adding more sections like the red one

  With Range("A2:A" & lr & " ,H2:H" & lr)

So, to add columns C and G it would become

With Range("A2:A" & lr & " ,H2:H" & lr & " ,C2:C" & lr & " ,G2:G" & lr)

However, if your columns of data are different in length and column H is not always the column to use to determine the last row, you could change how that is calculated.

<del>lr = Range("H" & Rows.Count).End(xlUp).Row</del>
lr = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row

 

[billandrew]

You are also a mind reader. The length of the columns are not the same.

Thank you very much.