VBA loop to colour cells containing a number over a given value.

DoctorPG

New Member
Joined
Feb 18, 2019
Messages
2
Hello! Very new guy to VBA here. I have a pretty simple problem (i think) i try to solve with some VBA code (could use conditional formatting as well, but i want to use VBA) I Have one column with a lot of rows of nummeric data, around 5000. I want make a code so when the value in the cell is > 330, it colours the given cell red. I tried some with the for loop and if - else commands, but i just cant get the syntax right i think.

Any help is greatly appriciated!

Kind regards
PG
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,655
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel
One option is
Code:
Sub DoctorPG()
   Dim Cl As Range
   
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      If Cl.Value > 330 Then Cl.Interior.color = vbRed
   Next Cl
End Sub
But you could do this without a loop like
Code:
Sub DoctorPG()
   Range("A1").AutoFilter 1, ">330"
   Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Interior.color = vbRed
   Range("A1").AutoFilter
End Sub
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,655
Office Version
  1. 365
Platform
  1. Windows
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,109,541
Messages
5,529,437
Members
409,877
Latest member
DDhol
Top