-
WSCardfaninKC
AskWoody LoungerYou can test Weekday(Date):
Code:Sub Test() Select Case Weekday(Date) Case vbSunday ' Code for Sunday Case vbMonday ' Code for Monday Case vbTuesday ' Code for Tuesday Case vbWednesday ' Code for Wednesday Case vbThursday ' Code for Thursday Case vbFriday ' Code for Friday Case vbSaturday ' Code for Saturday End Select End Sub
That worked great. Thanks!
-
WSCardfaninKC
AskWoody LoungerI just noticed the sentence “The spreadsheets I am copying from are in .xls format and the spreadsheet where I am pasting is a .xlsm spreadsheet.”. This means that the source and destination sheet don’t have the same number of rows. What happens if you copy a specific range, e.g.
Range(“A1:D1000”).Copy Destination:=ThisWorkbook.ActiveSheet.Range(“A1”)
(This instruction combines copy and paste)
I messed around with that a bit and I couldn’t get that to work. However, I may not have been doing it right. I’ll give it a shot and see what happens.
One question (and more info about what the macro is doing)…the spreadsheet that contains the data is not always named the same…it’s based on a selection made. The same selection also provide the information for the new spreadsheet, which is where the data will be pasted. Will that make any difference?
And also, how does the code know which “ThisWorkbook” to paste to? At this point in the code, the spreadsheet that has the data I want to copy is open and active.
Edited to add…Nevermind the above questions. It worked and I realize how it knew which workbook. It’s the workbook that contains the code. Thanks!
Thanks for being patient with me. I really do appreciate the assistance.
-
WSCardfaninKC
AskWoody LoungerDo you have merged cells in the source sheet or destination sheet? That is a known cause of problems when copying and pasting.
No, nothing is merged.
There is more code than what I’ve pasted, so I guess it’s possible that something elsewhere is causing the error. But this portion of the code is up near the top and the only thing before it is the opening of the other spreadsheet and the creation of a new worksheet.
I have also inserted at the beginning of the macro “Application.CutCopyMode = False” to clear out the clipboard.
-
WSCardfaninKC
AskWoody LoungerHere’s a macro using 10 colors. You can easily expand the number of colors used.
Very nice!
It does work in column A, which is fine. What part of the code “tells” it to work in that column? Is it this part: strCity = Cells(r, 1)
If so, then I could change the 1 to whatever column reference?
Thanks again for your help.
Edited to add:
Looks like the 1 referenced in my question above refers to a cell offset. I moved my list to Column F and changed it to a 6. It ended up still coloring Column A, but based on the data in Column F.
-
WSCardfaninKC
AskWoody LoungerI’m using 2007. I didn’t consider the human limitations. Thanks for pointing that out.
So no that I know they need to be grouped, is there a way to assign various colors? And if there are more in my list, I can just start over with the color assignments.
-
WSCardfaninKC
AskWoody LoungerI used to encounter this problem quite a bit with older versions of Excel. I never found out what caused it, but the remedy for me that worked every time was to uninstall (or delete) the printer and then add it back. That solved it for a few weeks and then when it cropped up again, I’d just repeat.
And I didn’t have to uninstall the drivers or anything…just delete the printer from the printer control panel.
-
WSCardfaninKC
AskWoody LoungerIf selecting an item in the combo box made the userform go away, the user wouldn’t be able to correct mistakes. So I’d place a command button cmdClose next to the combo box with the following code:
Code:Private Sub cmdClose_Click() Unload Me End Sub
Very nice.
That did it. Thanks for the tips and the help.
-
WSCardfaninKC
AskWoody LoungerYou can set the RowSource property of the combo box to R1:R49 in the Visual Basic Editor instead of in code, and you can set the ControlSource property to the address of the cell to which you want to link the value of the combo box, e.g. A1 – again, no code necessary.
Hey, thanks for the pointer on that. I got it to work, but it only populates the cell when I click the X to close it.
Now that I’m not using code, I’m not sure how to make the userform go away.
-
WSCardfaninKC
AskWoody LoungerHello again.
Thank you for trying to help me. It is much appreciated. I do not think conditional formatting will work. There will be some instances where I do not need a row colored even though it meets the conditions. The conditional formatting overwrites all formatting. I will keep digging around to see what I can come up with.
-
WSCardfaninKC
AskWoody LoungerThanks for the responses. I’ve tried conditional formatting and it won’t do what I’m trying to do. The cell contains both the date and time and I’m looking have it highlight based on the time only. In my format example above, let’s say I want it to highlight anything after 6:00PM. The example above would need to be highlighted regardless of the date. The spreadsheet is updated with new data daily and the time that needs highlighted will not always be the same.
I’ve tried entering the time into an inputbox and then concatenating it with a Today() formula and I can’t get that to work either. Copying and pasting the result as values gives me a number value that does not correspond with the correct date.
-
WSCardfaninKC
AskWoody LoungerCan you post a sample worksheet so we can see your current set up? Include a the before data and what you would like the results to look like when things are finished.
If you have 2007, please save the book to a previous version.
I have attached a sample spreadsheet. There are some blank cells in column C on purpose. Some come through that way.
Also, the physical address in column B may sometimes be different, and I need those to remain a separate line, but to also consolidate any email addresses associated with the customer name.
And sometimes the record is exactly the same as another one that appears.
Sure do appreciate it.
-
WSCardfaninKC
AskWoody LoungerUnfortunately, that article does not clearly explain how to enable parameters for web queries. I do that here:
http://www.jkp-ads.com/articles/webquery.aspHey thanks!
That looks to be a very clear explanation. I’ll mess around and if I have any questions I will let you know.
Thanks again!
-
WSCardfaninKC
AskWoody LoungerFebruary 24, 2009 at 4:24 pm in reply to: Conditional Formatting using multiple cell references #1148692Sorry to continue asking questions. I’m either doing this wrong or I’ve not explained what I’m looking for.
I am using Excel 2007 if that makes any difference.
I’ve pasted the formula you provided in the Rule Description part of the dialog. I do not receive an error, but no cells are formatting.
In 2007, it allows you to choose the area where you want the formatting to take place in a different step.
I have over 2300 rows and this will continue to increase each day as I add more data.
-
WSCardfaninKC
AskWoody LoungerFebruary 24, 2009 at 3:06 pm in reply to: Conditional Formatting using multiple cell references #1148671I apologize for not being more clear.
The cell that contains the date is in column A, and if the date matches any of the dates in AL4:AL30, I want the row colored a specific color.
-
WSCardfaninKC
AskWoody LoungerThat did it.
Thanks!
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |

Plus Membership
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Get Plus!
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Search Newsletters
Search Forums
View the Forum
Search for Topics
Recent Topics
-
*Some settings are managed by your organization
by
rlowe44
7 hours, 30 minutes ago -
Formatting of “Forward”ed e-mails
by
Scott Mills
9 hours, 44 minutes ago -
SmartSwitch PC Updates will only be supported through the MS Store Going Forward
by
PL1
10 hours, 9 minutes ago -
CISA warns of hackers targeting critical oil infrastructure
by
Nibbled To Death By Ducks
19 hours, 9 minutes ago -
AI slop
by
Susan Bradley
18 hours, 20 minutes ago -
Chrome : Using AI with Enhanced Protection mode
by
Alex5723
20 hours, 26 minutes ago -
Two blank icons
by
CR2
5 hours, 59 minutes ago -
Documents, Pictures, Desktop on OneDrive in Windows 11
by
ThePhoenix
1 day, 5 hours ago -
End of 10
by
Alex5723
1 day, 8 hours ago -
Single account cannot access printer’s automatic duplex functionality
by
Bruce
5 hours, 55 minutes ago -
test post
by
gtd12345
1 day, 14 hours ago -
Privacy and the Real ID
by
Susan Bradley
1 day, 4 hours ago -
MS-DEFCON 2: Deferring that upgrade
by
Susan Bradley
20 hours, 15 minutes ago -
Cant log on to oldergeeks.Com
by
WSJonharnew
1 day, 18 hours ago -
Upgrading from Win 10
by
WSjcgc50
6 hours, 5 minutes ago -
USB webcam / microphone missing after KB5050009 update
by
WSlloydkuhnle
9 hours, 38 minutes ago -
TeleMessage, a modified Signal clone used by US government has been hacked
by
Alex5723
2 days, 10 hours ago -
The story of Windows Longhorn
by
Cybertooth
1 day, 21 hours ago -
Red x next to folder on OneDrive iPadOS
by
dmt_3904
2 days, 12 hours ago -
Are manuals extinct?
by
Susan Bradley
5 hours, 41 minutes ago -
Canonical ditching Sudo for Rust Sudo -rs starting with Ubuntu
by
Alex5723
2 days, 21 hours ago -
Network Issue
by
Casey H
2 days, 8 hours ago -
Fedora Linux is now an official WSL distro
by
Alex5723
3 days, 9 hours ago -
May 2025 Office non-Security updates
by
PKCano
3 days, 9 hours ago -
Windows 10 filehistory including onedrive folder
by
Steve Bondy
3 days, 11 hours ago -
pages print on restart (Win 11 23H2)
by
cyraxote
2 days, 12 hours ago -
Windows 11 Insider Preview build 26200.5581 released to DEV
by
joep517
3 days, 13 hours ago -
Windows 11 Insider Preview build 26120.3950 (24H2) released to BETA
by
joep517
3 days, 13 hours ago -
Proton to drop prices after ruling against “Apple tax”
by
Cybertooth
3 days, 21 hours ago -
24H2 Installer – don’t see Option for non destructive install
by
JP
6 hours, 10 minutes ago
Recent blog posts
Key Links
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.