VBA Shade Row "x" color (light blue) based on cell value

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
Hi,
I searched on Google but could not get a VBA to work. I am using Office 2007. I am looking for a VBA to shade rows with "x" color (e.g., light blue) based on cell value. For example, if any row in Column A contains the value "Lesson", it will shade that row light blue. I know I can use Conditional Formatting for this and I have in the past, but I have to do 3 or 4 different steps to the same sheet and I am hoping to just create one long VBA that will do all 4 things instead of having to do multiple steps manually. (I have more than 200 sheets to do). Any help would be appreciated. Thanks in advance and Merry Christmas.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
How about
Excel Formula:
Sub Dannottheman()
   Dim Cl As Range
  
   For Each Cl In Range("A2", Range("A" & Rows.count).End(xlUp))
      If LCase(Cl.Value) Like "*lesson*" Then Cl.Resize(, 10).Interior.Color = rgbLightBlue
   Next Cl
End Sub
 
Upvote 0
Wow! Works great. Do you know why it may be shading all the rows up to column "J". I only need column "A" shaded.
 
Upvote 0
If you only want col A shaded remove
VBA Code:
.Resize(, 10)
 
Upvote 0
Never mind! I figured it out. I need to change "10" to "1" (Then Cl.Resize(, 1).
Thanks so much!
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
I'm sorry to ask one last thing. If I wanted to make that same row/text which has been colored "light blue" in bold, can you help me edit the VBA so it makes it bold at the same time? Otherwise, I would have to do conditional formatting by hand when it would be awesome to just use the same VBA.
 
Upvote 0
Try
VBA Code:
Sub Dannottheman()
   Dim Cl As Range
   
   For Each Cl In Range("A2", Range("A" & Rows.count).End(xlUp))
      If LCase(Cl.Value) Like "*york*" Then
         Cl.Interior.Color = rgbLightBlue
         Cl.Font.Bold = True
      End If
   Next Cl
End Sub
 
Upvote 0
Solution
My pleasure & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

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