Conditional Formatting using VBA

Michael515

Board Regular
Joined
Jul 10, 2014
Messages
136
Hi,

I would like to write a conditional format in VBA that does the following:

If any cell in M="True" highlight the entire row in a light blue shade and change the color to a dark blue shade (ex. $M2="True")

I would like to apply the rule to the entire column M.

I hope that was clear enough, thanks ahead of time for the help :)
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Why do you need VBA to do it?
Are you trying to include it in another program?

Note that you can get the VBA for this simply by returning on the Macro Recorder and record yourself doing it manually.
 
Upvote 0
Let me know if this works. This will highlight any row containing Cell "True" in Column M a dark blue.
Please test this in a separate instance of the document so that any fixes can be made and the original can be left in-tact.

Code:
Sub Coloring()

Dim RowCount as Long

RowCount = RowCount = ActiveSheet.Cells(ActiveSheet.Rows.Count, "M").End(xlUp).Row

For I = 1 to RowCount
If Cells(I,13)="True" Then Cells(i, 13).EntireRow.Interior.Color = 5296274
Next I

End Sub
 
Last edited:
Upvote 0
Code:
Sub Coloring()

Dim RowCount as Long

RowCount = RowCount = ActiveSheet.Cells(ActiveSheet.Rows.Count, "M").End(xlUp).Row

For I = 1 to RowCount
If Cells(I,13)="True" Then Cells(i, 13).EntireRow.Interior.Color = 5296274
Next I

End Sub
Just note that solution may not quite be what they are looking for, as it is not really Conditional Formatting.
Conditional Formatting is dynamic, where a VBA solution like that is not. It would NOT automatically update with a change to the data. The VBA code would need to be re-run to reflect any data changes (unless you were to put it in a Worksheet_Change event procedure - but then, why re-create the wheel when you can just use VBA to set up Conditional Formatting).

Also, loops in VBA are pretty inefficient, and it is generally a good idea to avoid them whenever possible, as they can slow down your code.
 
Upvote 0
Just note that solution may not quite be what they are looking for, as it is not really Conditional Formatting.
Conditional Formatting is dynamic, where a VBA solution like that is not. It would NOT automatically update with a change to the data. The VBA code would need to be re-run to reflect any data changes (unless you were to put it in a Worksheet_Change event procedure - but then, why re-create the wheel when you can just use VBA to set up Conditional Formatting).

Also, loops in VBA are pretty inefficient, and it is generally a good idea to avoid them whenever possible, as they can slow down your code.

You're absolutely correct. I am not the most efficient when it comes to VBA (nor wisest obviously), but I thought this code may be helpful for him to just hard-code it once or twice. If he wants to have it done conditionally, you can either macro recorder it or just have it done with one VBA code (which I do not have on me, I had this one on me and that's why I put it out there!)

Happy to help if I know how, but I think some more information would be necessary for this. Excel MVP's know best!!
 
Upvote 0
Hope you don't think I was criticizing you - I just just pointing out some limitations and considerations, so that the user can make the best well-informed choice that suits their needs.
Depending on the user's needs, the code may do the job for them, especially if they never have to worry about changing data and the data set is fairly small (they wouldn't notice a performance hit on a fairly small data set).

And don't worry, it happens to me too! There are users on here who are far smarter than me who put some of my answers to shame. But it is helpful, because I then learn new things too, which is one thing I really enjoy about this board.

And the more you do VBA, the better you will get at it. So keep plugging away and don't hesitate to keep posting your solutions! If someone post something better, then you have learned something and have a new tool in your belt!

By the way, one more little tip to make one of your lines a little shorter.
This line:
Code:
If Cells(I,13)="True" Then Cells(i, 13).EntireRow.Interior.Color = 5296274
can be shortened to
Code:
If Cells(I,13)="True" Then Rows(i).Interior.Color = 5296274
 
Last edited:
Upvote 0
Hope you don't think I was criticizing you - I just just pointing out some limitations and considerations, so that the user can make the best well-informed choice that suits their needs.

I appreciate any feedback on my coding, I'm here to learn just as much as the next person and there's no better way to learn than by seeing people criticize/tear apart my own code and see where I can increase efficiency and learn some new things. Thank you for pointing out that shortening statement, I'll be sure to use that statement in the future!

Looking forward to seeing more helpful posts of yours in the future =D
 
Upvote 0
I'm working with a workbook that pulls data of from an external database, and every time it refreshes so do the sheets. I have been using recorded macros and VBA to create additional columns that can help sift through the rest of the data much more quickly based certain conditions. I can't use normal conditional formatting because column M is a column created by a developed Macro (we are doing conditional formatting on a newly created column). I tried the VBA code given to me by the other user, calling it to run after column M was created, but it did not do the trick, I'm guessing that because of the newly created column. Here's how the calls are listed currently

Code:
Sub Auto_Open()
    Call RefreshData
    Call InsertConcatenate
    Call Macro3 'This Inserts Columns into Column M
    Call Macro5 
    Call Coloring
End Sub

Here's the code for Macro3:

Code:
Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+Shift+F
'
    Sheets("Sheet5").Select
    Columns("J:M").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.NumberFormat = "General"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Concatenated"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "Row Match"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Verifier"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "True/False"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=RC[-3]&RC[-1]"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J761")
    Range("J2:J761").Select
    Range("I762").Select
    Range("J761").Select
    Selection.AutoFill Destination:=Range("J761:J1000"), Type:=xlFillDefault
    Range("J761:J1000").Select
    Range("K2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(MATCH(RC[-1], 'Sheet2'!R2C12:R1048576C12,0),"""")"
    Range("K2").Select
    Selection.AutoFill Destination:=Range("K2:K1000")
    Range("K2:K1000").Select
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-2],'Sheet2'!R2C12:R1048576C13,2,FALSE),"""")"
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:L1000")
    Range("L2:L1000").Select
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]>0,RC[-1]=""SALE""),""True"", """")"
    Range("M2").Select
    Selection.AutoFill Destination:=Range("M2:M1000")
    Range("M2:M1000").Select
End Sub


Does this better explain why I'm having said issue?
 
Last edited:
Upvote 0
The fact that it is a newly created column shouldn't be an issue, as long as you run that code AFTER the code that creates the new column.
Did you try using the Macro Recorder? What you can do is run the code up to the point where you need the Conditional Formatting, stop it, and then record the macro there.
You can stop your code at a certain by using Breakpoints or simply temporarily adding an Exit Sub statement.

By the way, one thing you can do to make your code a little cleaner, is to fix it up after using the Macro Recorder. The Macro Recorder is very literal and records every single cell selection. Most of the time, you do not need to select cells to work with them. Usually, if you have one row that ends in ".Select", and the next row begins with "Selection" or "ActiveCell", those rows can usually be combined into one. Also, if you are using RC range references in your formulas, instead of using Autofill, you just assign the formula to the whole range at once.

So, your current code could be shortened to this:
Code:
Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+Shift+F
'
    Sheets("Sheet5").Select
    Columns("J:M").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("J:M").NumberFormat = "General"
    
    Range("J1").FormulaR1C1 = "Concatenated"
    Range("K1").FormulaR1C1 = "Row Match"
    Range("L1").FormulaR1C1 = "Verifier"
    Range("M1").FormulaR1C1 = "True/False"
    
    Range("J2:J1000").FormulaR1C1 = "=RC[-3]&RC[-1]"
    Range("K2:K1000").FormulaR1C1 = _
        "=IFERROR(MATCH(RC[-1], 'Sheet2'!R2C12:R1048576C12,0),"""")"
    Range("L2:L1000").FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-2],'Sheet2'!R2C12:R1048576C13,2,FALSE),"""")"
    Range("M2:M1000").FormulaR1C1 = "=IF(AND(RC[-2]>0,RC[-1]=""SALE""),""True"", """")"

End Sub
Lastly, I see that you are populating your formulas down to row 1000. If you are just doing that because you don't know where the data will end, you can make your code figure it out dynamically, and incorporate that into your code. Here is how:

It looks like your formula in column J is looking at columns G and I. So, if we can look at column G to determine where the last row of data is, we can do this before adding any of those formulas in column J to M to find our last row:
Code:
Dim lastRow as Long
lastRow = Cells(Rows.Count,"G").End(xlUp).Row
Then, we can change our formulas accordingly to use that, like this (repeat for the others):
Code:
    Range("J2:J" & lastRow).FormulaR1C1 = "=RC[-3]&RC[-1]"

See how you do with record your Conditional Formatting code, and write back here if you run into issues.
 
Upvote 0
Hi Joe,

Thank you so much for the advice on cleaning up my code. I am new to writing VBA, and I really appreciate you showing me a couple things that I can add to my "tool belt" for the future. Immediately after reading your comment, I went about and cleaned up all my modules that had recorded macros in them to reflect what you just taught me.

I tried re-recording the conditional formatting macro and this is what I have below:

Code:
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$M2=TRUE"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = -0.499984740745262
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.799981688894314
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

There are two problems though: The condition should be =$M2="TRUE" with the quotation, however I have had problems typing that into VBA, any advice?
The second problem is having the rule apply to the entire row if cell M# is TRUE, any suggestions for that?

I really appreciate your help! Thank you so much for taking time to critique and share your knowledge, it really helps with newbies like me!!!
 
Upvote 0

Forum statistics

Threads
1,213,583
Messages
6,114,489
Members
448,575
Latest member
hycrow

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
Back
Top