Excel Macro Help

  Fr0z3n 04:22 08 Jun 2004

Okay i have an excel spreadsheet and am stuck.
I have a list of numbers in a single column.
The macro needs to loop through this list and color the number in each cell an approriate color. E.g. if the cell had a "1" in it then this wud be green, "2" wud be red etc etc. I think i need some VBA code to do it. Can any1 save my life? Thanks in advance.

click here

  Fr0z3n 04:28 08 Jun 2004

How do i remove the title bar on a custom form i made in excel and the Visual Basic editor?
See link for details (links a JPEG file):

click here

  Rob_E 08:26 08 Jun 2004

Quickest and easiest is to use conditional formatting.

If you select the entire column, then on the menu bar select 'format'-'conditional formatting' you can specify the format (colour) for cells that meet a certain criteria.

So, for cells with value between 1 and 1 set the format to a foreground colour of Blue, you can then ADD more conditions, so for cells with a value between 2 and 2, set the format to a foreground colour to Red, etc.

or does it specifically need to be done using VBA?

  Simsy 10:54 08 Jun 2004

will only give you up to 3 formats, (i.e. in this instance, colours)

How many different colurs will you need?

Will possible range of numbers be consecutive?

What is the maximum number of colours you may require?

I'm not sure I can solve this myself, (In fact I'm not at all sure I can), but I'm sure someone can, and the preceeding questions I think will need to be answered.



  VoG II 11:15 08 Jun 2004

Sub MyCol()

Dim i As Integer

For i = 3 To Cells(Cells.Rows.Count, 5).End(xlUp).Row

Cells(i, 5).Font.ColorIndex = Cells(i, 5).Value

Next i

End Sub

  Simsy 18:56 08 Jun 2004

Any chance of an explanation, for learnign purposes?

I'm guesing that, in the line:

For i = 3 To Cells(Cells.Rows.Count, 5).End(xlUp).Row

the 3 is used because in the example spreadsheet the data starts on the 3rd row, and the 5 is used because it is in column E ,(i.e. the 5th column).

Have I got that right?



  VoG II 19:55 08 Jun 2004

Cells(Cells.Rows.Count, 5).End(xlUp).Row returns the last used row in Column 5 (E). So it loops from row 3 to whatever the last row is.

  Fr0z3n 21:39 08 Jun 2004

i have 10 different entries into a a column that i need to code. I need to to do done by vba so that i can place a button one one of my forms entitled "Color Code" that doers the above.

Any idea on the title bar problem on my custom forms ??

  Fr0z3n 21:41 08 Jun 2004

I've been told that im best to use the following structure for my purposes:

For each
If x then y
ElseIf a then b


can any1 elabortate plz

  VoG II 21:43 08 Jun 2004

Well the code you require is above. Modify to suit.

As far as I know you cannot "turn off" the title bar. You can set its title to nothing and there are some fairly involved (API) methods for removing the Close button. But you'll always have the title bar. Unless somebody knows differently...

  VoG II 21:45 08 Jun 2004

Case Select would be more appropriate than an If statement but, as above, you don't need either. Have you tried the code?

Is this Homework?

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

Halloween Photoshop & Illustrator tutorials: 20 step-by-step guides to creating spook-tacular…

iPhone X news: Release date, price, new features & specs

Comment créer, modifier et réinitialiser un compte Apple ?