# Revise formula (Stop color line)

#### ironsides

##### Well-known Member
Sub ColorLine()
Dim LR As Long, i As Long
LR = Range("g" & Rows.Count).End(xlUp).Row
For i = 7 To LR
With Range("g" & i)
If .Value = "" Then .Offset(, -6).Resize(, 28).Interior.ColorIndex = 4
End With
Next i
End Sub

Formula works well
except I want the color to stop on col AC
instead of continuing across entire sheet

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Code:
``````Sub ColorLine()
Dim LR As Long, i As Long
LR = Range("g" & Rows.Count).End(xlUp).Row
For i = 7 To LR
If Cells(7, i) = "" Then Range(Cells(i, 1), Cells(i, 29)).ColorIndex = 4
Next i
End Sub``````

Edited because I misread the with statement.

Last edited:
Thanks but the color line
still continues past the col specified
continuing across the entire sheet

ironsides

Thanks but the color line
still continues past the col specified
continuing across the entire sheet

ironsides

That's impossible. Is there other code that is going into this macro? The range that I specify:

Code:
``[COLOR=#333333]Range(Cells(i, 1), Cells(i, 29)).ColorIndex = 4[/COLOR]``

Is from column 1 (A) to column 29 (AC). It doesn't affect any other cells outside of that.

Code:
``````Sub ColorLine()
Dim LR As Long, i As Long
LR = Range("g" & Rows.Count).End(xlUp).Row
For i = 7 To LR
If Cells(i, 7) = "" Then Range(Cells(i, 1), Cells(i, 29)).ColorIndex = 4
Next i
End Sub``````

I did notice a small error so I just fixed that....

Replies
7
Views
383
Replies
11
Views
681
Replies
3
Views
447
Replies
2
Views
581
Replies
11
Views
619

1,219,792
Messages
6,150,288
Members
450,949
Latest member
faizanmalik10

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back