Hi Janet,
If you are using XL2007, then the range you are seleceting contains about 1
million cells, so this code will take a while.
If your data doesn't actually go to the end of Column N, you could speed it up
considerably by changing your first line to:
Range("N2:N20000").Select
If this range is enough to cover your data, it will run in about 1/50th of the
time the present code takes.
Also, you don't need to select cells to work on them.
You can start the code with:
For each Cell in Range("N2:N20000") (which would replace your first 2
lines.)
Also, it seems to me that the colors you are putting into o16, p16 etc, will be
overwritten every time the code executes its For/Next loop, and will be colored
only according to your very last data cell.
Regards - Dave.
Date: Mon, 30 Nov 2009 11:11:27 +0300
Subject: $$Excel-Macros$$ Help
From: janetdicks...@gmail.com
To: excel-macros@googlegroups.com
Hello Guys
I have created a useful code for my work, see below. but the problem: This code
takes almost 3minutes
to complete running. can anyone help me tweaking this to work much better.
Sub UseColorz()
Range("n:n").Select
For Each Cell In Selection
If Cell.Value > 0.02 And Cell.Value <= 0.03 Then
Cell.Interior.Color = RGB(255, 255, 0)
Range("o16").Interior.Color = RGB(255, 255, 0)
Range("p16").Value = " 0.02 to 0.03"
End If
If Cell.Value > 0.03 And Cell.Value <= 0.04 Then
Cell.Interior.Color = RGB(255, 0, 255)
Range("o17").Interior.Color = RGB(255, 0, 255)
Range("p17").Value = " 0.03 to 0.04"
End If
If Cell.Value > 0.04 And Cell.Value <= 0.05 Then
Cell.Interior.Color = RGB(0, 255, 255)
Range("o18").Interior.Color = RGB(0, 255, 255)
Range("p18").Value = " 0.04 to 0.05"
End If
If Cell.Value > 0.05 And Cell.Value <= 0.06 Then
Cell.Interior.Color = RGB(128, 0, 0)
Range("o19").Interior.Color = RGB(128, 0, 0)
Range("p19").Value = " 0.05 to 0.06"
End If
If Cell.Value < 0.01 And Cell.Value <= 0.02 Then
Cell.Interior.Color = RGB(192, 192, 192)
Range("o21").Interior.Color = RGB(192, 192, 192)
Range("p21").Value = " 0.059 to 0.066"
End If
If Cell.Value > 0.06 And Cell.Value <= 0.07 Then
Cell.Interior.Color = RGB(153, 153, 255)
Range("o22").Interior.Color = RGB(153, 153, 255)
Range("p22").Value = " 0.066 to 0.073"
End If
If Cell.Value > 0.07 And Cell.Value <= 0.08 Then
Cell.Interior.Color = RGB(128, 128, 0)
Range("o23").Interior.Color = RGB(128, 128, 0)
Range("p23").Value = " Greater Than 0.073"
End If
Next
End Sub
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
_________________________________________________________________
Looking to move this spring? With all the lastest places, searching has never
been easier. Look now!
http://clk.atdmt.com/NMN/go/157631292/direct/01/
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
