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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.....
 
Upvote 0
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??
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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