Working through VBA Conditional Formatting??

[DougStroud]

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

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

 

[Norie]

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.

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]

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]

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.

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.

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

 

[DougStroud]

Oh,
much clearer now....


thanks!