VBA conditional Formatting

[GordonLS]

Good afternoon. I need to have rows filled based off Order Number as follows:

I can do this with conditional formatting but would like to execute in VBA.

 

[Joe4]

Easiest way is to apply the Conditional Formatting with VBA.
Otherwise, you would probably have to use loops in VBA, which are needlessly slow and inefficient, when you have better options.

The other advantage to Conditional Formatting is that it is dynamic.
There is dynamic VBA code too, but you would have to re-evaluate every row anytime a change is made, which isn't that efficient.

 

[GordonLS]

So how would I apply the Conditional Formatting with VBA? I'm a novice at VBA.

 

[Joe4]

A great tool for Novices (and Pros alike) is the Macro Recorder.
If you turn on the Macro Recorder, and record yourself performing the Task manually, it will give you much of the VBA code you need.
Then just turn it off and view your code.

The caveat is that the Macro Recorder is very literal, so sometimes records things you don't really need (like screen scrolling), and can often be cleaned up a little.
It can usually also be made to be more dynamic with a few simple edits, if needed.

Give it a try, see how far you get, and if you run into any issues or need help "tweaking" the code, post back here to this thread with your code and question.

 

[GordonLS]

Thanks Joe.

 

[Joe4]

You are welcome.

Even after all these years, I still use the Macro Recorder to get snippets of code (it sure beats trying to remember it or having to look it up!).

 

[GordonLS]

Good morning, so I am trying to clean up the code for this. This code will fill rows with color based off alternating order numbers from Row 2 thru Row 500.


Rows("2:500").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(SUMPRODUCT(--($e$2:$e2<>$e$1:$e1)),2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With



What I would like to do is incorporate the following so that the rows selected are only the rows that have data in Column A.
'Range("A1").Select
'Selection.End(xlDown).Select
'lastRow = ActiveCell.Row

But I need the alternating fill to work from row A1 through the "lastRow" and for as many columns that have data.

Just let me know if more information is needed.

Gordon

 

[Joe4]

Good morning, so I am trying to clean up the code for this. This code will fill rows with color based off alternating order numbers from Row 2 thru Row 500.


Rows("2:500").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(SUMPRODUCT(--($e$2:$e2<>$e$1:$e1)),2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With



What I would like to do is incorporate the following so that the rows selected are only the rows that have data in Column A.
'Range("A1").Select
'Selection.End(xlDown).Select
'lastRow = ActiveCell.Row

But I need the alternating fill to work from row A1 through the "lastRow" and for as many columns that have data.

Just let me know if more information is needed.

Gordon

Re-posted here: Conditional Formatting VBA

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

 

[GordonLS]

I added to an old post and then thought I should create a new post.

Thanks,

 

[Joe4]

I added to an old post and then thought I should create a new post.

That's fine, but then you would need to indicate in the original that you posted a new thread on it (and post a link to it), so it does not get answered in both places (as there is a good chance that people may only see one of the questions and not both).

People sometimes get upset when they spend time developing a solution, and then come to find out that the exact same question has already been answered elsewhere in another thread. So we want to avoid that situation.