Need Conditional Format help

K

kelly1

Well-known Member
Joined
May 11, 2003
Messages
525
I have been using Conditional Format using 3 colours but noticed it severely slows the spreadsheet down.

Everything I do seem to take for ages, and this is on current spec systems.
When I removed the formatting the speed dramatically increases, is this normal?
The organisation I work for really need this facility on.

Would VBA be any better?

What I need is a different colour for the row from Column A to K based on text input in Column I which are “OK”, “Finish”, “Incident”,

I already have this code on the worksheet:


Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count > 1 Then Exit Sub
If .Column = 1 Then .Offset(0, 5).Value = Format(Date, "mm/dd/yy")
If .Column = 8 And .Value = "Y" Then
Range("A" & .Row).Copy _
Worksheets("Incidents").Range("A65536").End(xlUp).Offset(1, 0)
Sheets(2).Select
MsgBox ("Transfer to Incidents Sheet Successful")
End If
End With
End Sub


Any help appreciated

Kelly
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Thinking out aloud again but perhaps a Workshetet_SelectionChange with a Union and Select Case??? May be babble ....
 
Upvote 0
It may be something to do with your range selection below
It will possibly work quicker if you reduce it to ("A1000") for example


Worksheets("Incidents").Range("A65536").

Corleone
 
Upvote 0
That VBA is not really slowing it, its the conditional format that seems to be slowing it down, once removed it goes quick again.

Once the condition is met, it takes about 10 seconds to change colour.

I am using Excels formating and wondered if VBA would be better?

At the moment we are using 2000 rows but it is likely to go much higher.
 
Upvote 0
Here's what I was babbling on about - VBA conditional formatting example

Code: 
Private Sub Worksheet_Change(ByVal Target As Range) 
Dim icolor As Integer 
If Not Intersect(Target, Range("A1:A2000")) Is Nothing Then 
Select Case Target 
  Case 1 
     icolor = 17 
  Case 2 
     icolor = 18 
  Case 3 
     icolor = 19 
  Case 4 
     icolor = 20 
  Case 5 
     icolor = 21 
  Case Else 
   icolor = 16 
End Select 
  Target.Interior.ColorIndex = icolor 
End If 
    
End Sub

Not sure if it'll be any faster than xl's cond formatting. You're s/s may be slow if you're calculating a lot of formulae. Also have a look at this link http://support.microsoft.com/default.aspx?scid=kb;en-us;215783&Product=xlw2K
 
Upvote 0
I guess it means formatting over 2050 rows rather than 2050 rows on the spreadsheet.

I have one S/S with 4000 rows and that cond/formt ok, but I suppose its only formatting about 1500 rows.

So what is the solution if you need to format 5000+ rows?
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
History of Dates to be traced in Excel (2010) in DD/MM/YYYY format
2
Replies
11
Views
364
MARK858
MARK858
S
  • Question
Creating a New Column from Existing Column that pulls one word from a Description
Replies
4
Views
140
storocco1
S
S
  • Question
Count coloured cells in a range which has been conditionally formatted
Replies
1
Views
315
awoohaw
awoohaw
S
  • Question
VBA To Filter Conditionally Formatted coloured cells in multiple columns
Replies
1
Views
127
bobsan42
bobsan42
P
  • Question
Applying conditional formatting to merged cells
Replies
1
Views
266
Joe4
Joe4

Forum statistics

Threads
1,214,652
Messages
6,120,746
Members
448,989
Latest member
mariah3

Share this page

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