Adjust a range

[paulsolar]

Hi All

I'm having a bad day, been trying to do the below using conditional formatting and cant get it right.

How do I adapt the blow to highlight only columns A-L rather than th whole row?

kind regards

Paul

Sub color()


  Dim color As Integer


  For Each cell In Sheet2.Range("A2:A65536")


    If IsEmpty(cell) Then GoTo nextcell:
    If Not IsNumeric(cell.Value) Then GoTo nextcell:


    If cell.Value > 0 Then
      color = 37  
    End If  
    cell.EntireRow.Interior.ColorIndex = color
  nextcell:
  Next cell


End Sub

 

[Joe4]

Change this line:

    cell.EntireRow.Interior.ColorIndex = color

to this:

    Range(Cells(cell.Row,"A"),Cells(cell.Row,"L")).Interior.ColorIndex = color

 

[njimack]

Change this:

cell.EntireRow.Interior.ColorIndex = color

to this:

Range(cells(cell.Row,1),cells(cell.Row,12)).EntireRow.Interior.ColorIndex = color

There are more efficient ways of doing this though - you should determine the used range rather than running the code for 65,000 rows, and I'd use autofilter and SpecialCells(xlCellTypeVisible) rather than looping through the entire range

 

[paulsolar]

Hi Both,

Many thanks for your help, I thought I'd never get home tonight. Been stuck on this for an hour