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.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,833
Office Version
  1. 365
Platform
  1. Windows
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
 

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
Wow! Works great. Do you know why it may be shading all the rows up to column "J". I only need column "A" shaded.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,833
Office Version
  1. 365
Platform
  1. Windows
If you only want col A shaded remove
VBA Code:
.Resize(, 10)
 

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007

ADVERTISEMENT

Never mind! I figured it out. I need to change "10" to "1" (Then Cl.Resize(, 1).
Thanks so much!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,833
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007

ADVERTISEMENT

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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,833
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,833
Office Version
  1. 365
Platform
  1. Windows
My pleasure & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,356
Messages
5,595,681
Members
414,009
Latest member
SNesbyCarr

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
Top