Working through VBA Conditional Formatting??

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,963
I would like to add a line of code to my procedure that if the value in column C is greater than 998 then color the row in w/ black.
In this particular case, the row I am working w/ is row #2.
A2:D2 would be black. The entire range will be A2:D20. And this is subject to change everytime...

I am working on learning VBA, so rather than write the code for me, if anyone responding would assist in the "parts" of the line of code rather than write it for me please.

My first question is in writing an IF formula-- Do I use a Range Object for column C or Cells Property. I know that Cells is a property, not an object. But this is foggy for me currently.

Here is my existing code, which may help in understanding better what I am attempting to add.

The code at the bottom that is commented out is my beginning point.

Thanks for any and all assistance.

ds

Code:
Option Explicit

Sub PriceLabels()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LRow1 As Long, LRow2 As Long

Set ws1 = Sheets("Update")
Set ws2 = Sheets("PriceLabels")

LRow1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
LRow2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row

ws2.Activate
ws2.Range("a2:d" & LRow2).Clear

ws2.Range("A1:D1") = Array("Item#", "Record Description", "Qty", "Price")
        
    ws1.Range("A2:B" & LRow1).Copy ws2.Range("A2")
    ws1.Range("G2:G" & LRow1).Copy ws2.Range("C2")
    ws1.Range("L2:L" & LRow1).Copy ws2.Range("D2")

With ws2
        Rows("1:1").HorizontalAlignment = xlCenter
        Rows("1:1").Font.Bold = True
        Cells.Columns.AutoFit
        Rows("1:1").HorizontalAlignment = xlCenter
        Rows("1:1").Font.Bold = True
End With

'If Cells("C2:C")>998
End Sub

'If Cells(i, "AA") = "" And Left(Cells(i, "Y"), 2) = "~P" Then Range(Cells(i + 1, "AA"), _
'Cells(i + 1, "AB")).Copy Range(Cells(i, "AA"), Cells(i, "AB"))
' Range("A2:D2").Select
'    Selection.FormatConditions.Delete
'    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$C2>998"
'    Selection.FormatConditions(1).Interior.ColorIndex = 1
'    Selection.Copy
'    Range("A3:D20").Select
'    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
'        SkipBlanks:=False, Transpose:=False
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,274
Office Version
  1. 365
Platform
  1. Windows
Doug

For a start you shouldn't need to be activating/selecting anything.

Also here you have used with but you haven't actually used ws2 to reference the Rows or Cells.
Code:
With ws2
        Rows("1:1").HorizontalAlignment = xlCenter
        Rows("1:1").Font.Bold = True
        Cells.Columns.AutoFit
        Rows("1:1").HorizontalAlignment = xlCenter
        Rows("1:1").Font.Bold = True
End With

Cells is a property, but what it returns is a Range object.

As to the conditional formatting, why not just apply it to the whole range in one go rather than just A2:D2 and then copying?
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,963
Hi Norie,
thanks for responding.

Regarding your individual points:

The selecting and Activating is just there from the Macro recorder. I just left it intact so I could reference the whole code... as I began to pull out the important parts-- this is what you are referring too, yes?

I have only an inkling of an idea how "With" works... I think I picked this up from a book I have-- Bill and Tracy's VBA. I thought I was sort of augmenting this section out by itself this way.

You are exactly correct on the conditional formatting. I only referenced this because the way I figure this stuff out is to concentrate on one small area, get that working and then apply it to my final goal.

The point about the cells returning a Range object helps. How or why is Cells used rather than Range or Vice Versa, e.g. what situations are better suited for one or the other?

thanks,

ds
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,274
Office Version
  1. 365
Platform
  1. Windows
Doug

I can't really give a comprehensive overview of why you would use Cells over Range.

For some people it's just a personal preference.

One reason for using Cells is that rather than having to use the letter(s) to identify a column you can just use a number.

For example.
Code:
For I = 1 To 256
     Msgbox Cells(1, I).Address
Next I

As to using With, what you need to do within the With...End With is use the dot qualifier to associate the objects with ws2 or whatever.

So to ensure the code was working with rows on ws2 your code would look this.
Code:
With ws2
        .Rows("1:1").HorizontalAlignment = xlCenter
        .Rows("1:1").Font.Bold = True
        .Cells.Columns.AutoFit
End With
 

Watch MrExcel Video

Forum statistics

Threads
1,128,107
Messages
5,628,731
Members
416,333
Latest member
Time2Learn

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