Excel Macro Help

  Fr0z3n 04:22 08 Jun 04
Locked

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 04

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 04

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 04

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.

Regard,

Simsy

  VoG II 11:15 08 Jun 04

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 04

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?

Regards,

Simsy

  VoG II 19:55 08 Jun 04

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 04

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 04

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
Next

etc.....

can any1 elabortate plz

  VoG II 21:43 08 Jun 04

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 04

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.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…