write ing my own formatting macro

[RoydenC]

I am following a few clues gleaned from this forum, and am trying to write my own macro for extending the conditional format function to more than 3 options.
Having recorded the macro for 3 options - and I now go to edit the macro, and can plainly see the recurring nature of the code. I would presumably simply copy using cut and paste the repeated code which is appearing in the editor, and change the conditions etc etc - and "voila!"- theres the macro??
The problem is that I am formatting cells by colour related to todays date - and want a fine grained formatting (ie subdivided by the week - but extending say 3 months into the future). The macro code refers to "Colorindex=44" etc - which refers to the color palette.
How can I establish what "Colorindex" all the colors in the palette are (without a trial and error investigation)?
I am of course assuming I have not already barked up the wrong tree with my whole approach in the first place.
Any help most appreciated

 

[applebyd]

IIRC

The colours are in order on the palette
top to bottom left to right 1-40

The higher numbers must refer to the 10 custom options.

Anyone care to elaborate?

It's a start anyway


DaveA

 

[Richie(UK)]

Hi RoydenC,

Try this in a on a blank sheet and then refer to it as necessary:

<pre>
Sub ColourTable()
Dim i As Integer

With Worksheets("Sheet1")

' interior cell colour
For i = 1 To 56
Range("D" & i).Interior.ColorIndex = i
Range("E" & i).Value = i
Next i

' font colour
For i = 1 To 56
Range("F" & i).Font.ColorIndex = i
Range("F" & i).Value = i
Next i

End With

End Sub

</pre>

HTH

 

[RoydenC]

Thanks for the two replies,
I am going to have to piece the colour numbers together as best I can - as far as Richie's suggestions I am not sure of their significance - perhaps you could advise on how this would work in relation to my initial objective.
Remember I am not a macro boffin - yet - far from it,
Thanks

 

[Legacy 98055]

Press ALT-F11,
Click on help up top,
Search "ColorIndex"
There is a nice help file which lists all of the colors and the associated indexes.
Tom

 

[Richie(UK)]

Hi RoydenC,

What the macro listed above does is produce a column of the various colours available and a column alongside it with the associated reference number.

You could print it out or have the workbook open while working on your project - either way you have a nice visual aid to indicate the various colour codes.

Give it a try!

 

[RoydenC]

Thanks Richie,
...Off I go on another work tangent - so there will be a slight deviation from this train of thought - will refresh the topis later after I have had time to get back to it,
thanks again