Background Color Multiple Columns.

gripper

Board Regular
Joined
Oct 29, 2002
Messages
176
I am working on the below code. My goal is to apply a light background color to 6 specific columns.
I want this background color to only occur in these columns to the last row. I don't want it to extend past the last row

When I apply this code I get an error if I reference the "lrow". If I run this by just the whole columns reference it works but the coloring will go way below my last row.

Only other issue is a loop which is a bit nutty for a 1000 rows by 6 columns.

VBA Code:
Range("AK:AP" & lrow)).Interior.Color = RGB(255, 228, 196)

Thank you
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Assuming all the columns AK:AP have the same number of filled cells as does Column AK
Try this:
If not let me know and we can do it another way
VBA Code:
Sub Fill_With_Colors()
'Modified 12/23/2020  10:32:03 PM  EST
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "AK").End(xlUp).Row
Range("AK1:AP1").Resize(Lastrow).Interior.ColorIndex = 20
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
Now if all those columns had different range of value you could use this:
VBA Code:
Sub Fill_With_Colors()
'Modified  12/23/2020  10:51:35 PM  EST
Application.ScreenUpdating = False
Dim Lastrow As Long
Dim i As Long
For i = 37 To 42
    Lastrow = Cells(Rows.Count, i).End(xlUp).Row
    Cells(1, i).Resize(Lastrow).Interior.ColorIndex = 20
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thank you for expanding with other options to use within other scenarios. I appreciate it.

I have a sheet that has this exact issue I will be working on in the future. So again thank you for the timely advice.
 
Upvote 0
Thank you for expanding with other options to use within other scenarios. I appreciate it.

I have a sheet that has this exact issue I will be working on in the future. So again thank you for the timely advice.
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top