Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Colored day ranges 3

Status
Not open for further replies.
sucoyant

sucoyant

IS-IT--Management
Sep 21, 2002
213
US
Hello all!

I have a spreadsheet with named ranges. These are Day1, Day2, Day3... and so on.

What I would like to do is this: When the user opens the excel spreadsheet I want only the current Day range (I.e Day21 = 1/21/04) to be white. I would like all of the other Day ranges to be grey.

I imagine this will involve some VBA.

I would appreciate any help in this matter.

----------------------------------------
Buddha. Dharma. Sangha.
 
Sort by date Sort by votes
Sounds like conditional formatting with the case of if cell contents are equal to TODAY then colour white.
 
Upvote 0 Downvote
The contents of the cell is not the date. I have a range of cells with a the naming schema of Day1, Day2, Day3... so this conditional formatting will not work. I already tried it. :-(

I appreciate the reply though!

----------------------------------------
Buddha. Dharma. Sangha.
 
Upvote 0 Downvote
Hi sucoyant,

Depending on what exactly you are doing, you may be able to use conditional formatting, but if not, then something like this ..

Code: 
For each N in ThisWorkbook.Names
    If Val(Mid(N.Name,4)) = Day(Date) Then
        Range(N).Interior.ColorIndex = 2
    Else
        Range(N).Interior.ColorIndex = 16
    EndIf
Next

.. should do it. If any of your ranges overlap, you'll have to amend it slightly to make sure the right color takes effect.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.
 
Upvote 0 Downvote
Rather than today() use in the condition

="Day" & Day(Today())

dyarwood
 
Upvote 0 Downvote
Thank you for the replies!

My spreadsheet looks like this:
spreadsheet.gif

You can see the highlighted area is named Day30.

I'm having a bit of a problem understanding the VBA code:
Code: 
For each N in ThisWorkbook.Names
    If Val(Mid(N.Name,4)) = Day(Date) Then
        Range(N).Interior.ColorIndex = 2
    Else
        Range(N).Interior.ColorIndex = 16
    EndIf
Next

How would I retrofit the code to my need? The sheet name is Daily_Work, and as I said before the ranges are Day1, Day2, Day3.

I appreciate all of your help. VB/VBA is not my thing. I know C++... but not VB/VBA.

Thank you all very much.

----------------------------------------
Buddha. Dharma. Sangha.
 
Upvote 0 Downvote
I keep getting this error:
excelerror.gif



It highlights the first line of code.

I have
Code: 
For each N in DailyWork.Names

----------------------------------------
Buddha. Dharma. Sangha.
 
Upvote 0 Downvote
Alrighty! Excellent.

It works when I call the function in the "Immediate" window.


Now, is there any way that I can make it automatically do this every time its opened?

----------------------------------------
Buddha. Dharma. Sangha.
 
Upvote 0 Downvote
Nevermind. It works like a charm.

Thank you all for making this possible. I appreciate it very much!

----------------------------------------
Buddha. Dharma. Sangha.
 
Upvote 0 Downvote
Dyarwood had you on the right track for a conditional formatting reply. All you had to do was select the entire range C4:Q46 (I'm assuming the first cell below Admin is C4), do Format / Conditional Formatting / Change 'Cell value is' to 'formula is' and then put in the following formula:-

=DAY(TODAY())<>$B4

This assumes that the figures in Col B are real numbers, eg 1,2,3,4,5 etc and are not something else that just look like them. This would change every row that is not equal to the current day to grey, leaving the other white. the partial locking of the reference (ie the $B) ensures that all the cells are formatted in relation to what is going on in the 'cell for that row' in Column B

Would have thought changing it slightly though to actually highlight the current day in perhaps a pale yellow would have looked nice, in which case formula needs to be:-

=DAY(TODAY())=$B4

Regards
Ken..............

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]

----------------------------------------------------------------------------
 
Upvote 0 Downvote
Hi sucoyant,

Glad you've got it working.

For the record you CAN do this with Conditional Formatting.

In your example, set the colours as you want them for 'not today', then select C4:Q34 (not absolutely sure of the row numbers because your image doesn't include them) and, in conditional formatting, choose Formula Is and in the Formula box enter =$C3=DAY(TODAY()), choose your formatting for today, and press Enter.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.
 
Upvote 0 Downvote
LOL Ken - Snap!

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.
 
Upvote 0 Downvote
Might want to shift your column over by 1 Tony :)

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]

----------------------------------------------------------------------------
 
Upvote 0 Downvote
Oops! [blush]

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.
 
Upvote 0 Downvote
Wow. Thank you all for this valuable information!

----------------------------------------
Buddha. Dharma. Sangha.
 
Upvote 0 Downvote
Hi Sucoyant -
This question is unrelated but when you have time could you please detail how you displayed your &quot;Error Message&quot; in Tek Tips-
I've tried a couple methods with &quot;zero&quot; success.
Thanks
tproud20@yahoo.com
 
Upvote 0 Downvote
brian4321: It's simple. First, make sure that the error is the window that currently has focus. Then hold down &quot;Alt&quot; and press &quot;Print Screen&quot;. This Alt+PrtScrn combo will capture only what's currently in focus.

Next open paint, and hit paste. Done!

As for making it viewable here, I have my own website (Shameless plug) that a friend and I own. We upload all of our junk there.

To show an image take a look at the &quot;Process TGML&quot; documentation:
----------------------------------------
Buddha. Dharma. Sangha.
 
Upvote 0 Downvote
Hi Sucoyant,
Thanks for the quick response
I'm familiar with the first 2 points... For me the missing piece of the puzzle was that I didn't know I required a site to upload the image to.
Once I get that in place I'm set.

A picture is worth a 1000 words

Thanks again for helping out the new guy.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question
Excel Networking days
Replies
4
Views
213
SkipVought
S
davlak39
  • Locked
  • Question
Word macro for printing pages with colored font
Replies
5
Views
100
macropod
macropod
arvarr
  • Locked
  • Question
Excel - Find Date/Days to use the values
Replies
3
Views
99
SkipVought
S
slamminhammo
  • Locked
  • Question
.7 Day Flder in Outlook 2010
Replies
2
Views
37
strongm
strongm
MITTENCAT
  • Locked
  • Question
Planning activities on difrent dates - but two activities only per day 1
Replies
16
Views
123
MITTENCAT
MITTENCAT

Part and Inventory Search

Sponsor

Back
Top