How to change conditional formatting into VBA.

bat18

Board Regular
Joined
Mar 29, 2010
Messages
89
I have a spreadsheet where columns A:H have 3 different long conditional formats based on values in different columns producing a different colour depending on the value.

This is making the spreadsheet very slow and i could also do with introducing a 4th colour, is there a way I could move this all to VBA and when i enter my values i simply click a button to run the macro which will fill the cells with the correct colour.

2 questions: Will this stop the spreadsheet from performing slowly and what is the type of VBA to write. Any help would be really appreciated.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
It depends on exactly what you want....

1. do you want it to update the color immediately after changing a value?

2. Or do you want to change SEVERAL values without updating the colors, then at some time later run the macro to update all the colors?

If it's 1, then NO, vba will not be any faster than conditional formatting
If it's 2, then it will still not be any faster, but it will be more efficient because it only runs when you tell it to, instead of every time a cell changes.


Also, you might not need VBA for a 4th color...
Intrigued.....
 

bat18

Board Regular
Joined
Mar 29, 2010
Messages
89
When I mean speed I mean the whole spreadsheet has become slow to move around in due to the conditional formatting. When I try to scroll through the sheet it is infuriatingly slow to move around,

I will explain the problem a little bit better

Every day in column Y i paste in values I have obtained from a different source. Say i paste 150.67 into Y100 it will try to match the value in one of 4 columns U:X. Say it matches to X567 then cells A567:H567 will turn yellow, now i need a different colour depending on which column it matches to. And if there is no matching value the cells must remian blank. So is this any clearer??
 

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
874

ADVERTISEMENT

I believe you'll achieve better speed if you use the VBA. Part of what you're dealing with is just the "weight" of having many formulas in your sheet.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Here's a basic example syntax for VBA conditional formatting
Code:
Sub MyFormat()
Dim MyRange As Range, C As Range
Dim MyColor As Variant
Set MyRange = Range("A1:A100")
For Each C In MyRange
    Select Case C.Value
        Case ""
            MyColor = xlNone
        Case Is < 10
            MyColor = 6
        Case Is < 50
            MyColor = 12
        Case Is < 100
            MyColor = 3
        Case Else
            MyColor = xlNone
    End Select
    C.Interior.ColorIndex = MyColor
Next C
End Sub
 

bat18

Board Regular
Joined
Mar 29, 2010
Messages
89

ADVERTISEMENT

Thats exactly what I thought so any ideas on how i move these conditional formatting into VBA

=IF(ISERROR(VLOOKUP($U2, $Y$1:$Y$29999, 1, FALSE))=FALSE, TRUE, FALSE)

=IF(ISERROR(VLOOKUP($V2, $Y$1:$Y$29999, 1, FALSE))=FALSE, TRUE, FALSE)

=IF(ISERROR(VLOOKUP($W2, $Y$1:$Y$29999, 1, FALSE))=FALSE, TRUE, FALSE)

I would also like a 4th one with a different colour (another reason VBA is what i need). Cheers for your help
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
ok, number one, those are greatly overcomplicated. probably causing alot of overhead...

=IF(ISERROR(VLOOKUP($U2, $Y$1:$Y$29999, 1, FALSE))=FALSE, TRUE, FALSE)

can be just

=MATCH($U2,$Y$1:$Y$29999,0)
 

bat18

Board Regular
Joined
Mar 29, 2010
Messages
89
Brilliant, thats absolutely true. Still very slow and I think a button and some VBA would make everything a lot easier. I need to apply the conditional formatting to all cells in columns A:H as well rather than just one column.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Is it absolutely vital that you have a different color depending on which column the value is in?
Is it not sufficient to have 1 color saying that it was found in one of the 4 columns?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,709
Messages
5,597,690
Members
414,164
Latest member
ARTW

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