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!

  • 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

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.
 
Sounds like conditional formatting with the case of if cell contents are equal to TODAY then colour white.
 
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.
 
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.
 
Rather than today() use in the condition

="Day" & Day(Today())

dyarwood
 
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.
 
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.
 
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.
 
Nevermind. It works like a charm.

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

----------------------------------------
Buddha. Dharma. Sangha.
 
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]

----------------------------------------------------------------------------
 
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.
 
LOL Ken - Snap!

Enjoy,
Tony

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

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

----------------------------------------------------------------------------
 
Oops! [blush]

Enjoy,
Tony

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

----------------------------------------
Buddha. Dharma. Sangha.
 
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
 
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top