VBA Excel Conditional Formatting <--rookie here

[Dan]

Hello -

I would like to use VBA for some Condtional Formatting.

I have 2 columns, A and B. From row 5 down, I would like to have
cells in column A be Conditional Formatted.

For example:
* If A5>B5 then color RED
* If B5<A5 then color GREEN
* ...and so on till the end of data in Column A.

Thanks for any assistance.
Dan

 

[Gord Dibben]

Did you read your description before posting?

A5>B5 is the same as B5<A5

A typo or?


Gord Dibben MS Excel MVP

 

[SeanC UK]

Hi Dan,

Try this, it should work for the formatting conditions you require from cell
A5 to the bottom of column A.

Public Sub MyConditionalFormatting()
Range(Cells(5, 1), Cells(Rows.Count, 1)).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A5>$B5"
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A5<$B5"
Selection.FormatConditions(2).Interior.ColorIndex = 4
End Sub

As a rookie I would suggest using the Record Macro facility to see roughly
how it happens when you manually enter such code, and build up from there.
Although this won't always give you the exact results you desire, it is a
good starting place for syntax and object names, methods and properties.

The code above should appear from a recorded macro, but I have amended the
code because you might find that a $ appears before the row numbers when you
click a cell to build the formatting formula. If this happens then all the
cells from A5 down would react on the contents of A5 and B5 only. Removing
that $ and running the code means that on subsequent rows the row number is
incremented, so that A6 reacts to the contents of cells A6 and B6.

The second line, with the Delete in it, means that any existing conditional
formats are removed, otherwise you might run into a problem with a max of 3
in Excel 11 and earlier versions. I can't remember the limits of Excel 12 off
the top of my head.

The first line simply selects the cells from A5 to the last row in column A.

The remaining line are self explanatory, they just add the rules and
conditions.

I would just point out that you haven't included A5=B5 in your conditions,
in case you meant to.

Hope this helps,

Sean.

 

[Dan]

Hi Dan,

Try this, it should work for the formatting conditions you require from cell
A5 to the bottom of column A.

Public Sub MyConditionalFormatting()
Range(Cells(5, 1), Cells(Rows.Count, 1)).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A5>$B5"
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A5<$B5"
Selection.FormatConditions(2).Interior.ColorIndex = 4
End Sub

As a rookie I would suggest using the Record Macro facility to see roughly
how it happens when you manually enter such code, and build up from there.
Although this won't always give you the exact results you desire, it is a
good starting place for syntax and object names, methods and properties.

The code above should appear from a recorded macro, but I have amended the
code because you might find that a $ appears before the row numbers when you
click a cell to build the formatting formula. If this happens then all the
cells from A5 down would react on the contents of A5 and B5 only. Removing
that $ and running the code means that on subsequent rows the row number is
incremented, so that A6 reacts to the contents of cells A6 and B6.

The second line, with the Delete in it, means that any existing conditional
formats are removed, otherwise you might run into a problem with a max of 3
in Excel 11 and earlier versions. I can't remember the limits of Excel 12 off
the top of my head.

The first line simply selects the cells from A5 to the last row in column A.

The remaining line are self explanatory, they just add the rules and
conditions.

I would just point out that you haven't included A5=B5 in your conditions,
in case you meant to.

Hope this helps,

Sean.

--
(please remember to click yes if replies you receive are helpful to you)









- Show quoted text -

Great write-up, thanks Sean. I did try the "record" macro to try and
learn, but I used a copy and paste method to get the row to highlight
and got a little confused from there. I like how you use the Count
function in your code.

Regards,
Dan

 

[SeanC UK]

You're wekcome.

Be careful with the count usage, it is convenient to get to the last row or
column, but sometimes, if used in a loop, or for adding formulae to cells, it
can add large overheads to running times (for loops) or file sizes (when
adding a formula). In these cases you should look for alternative methods so
that you only process the required number of rows or columns. As you will
find, using the conditional formatting for the entire column does not
generate excessive overheads, the file size won't alter much.

Keep playing with it, I'm sure you'll have much fun.

(please remember to click yes if replies you receive are helpful to you)