Conditional Format - range

Dear all

I am attempting to learn macros (slowly! )... I have recorded a macro to colour a column if the text length = 15. However, I want to automatically colour for the column for the used cells (which will change each month).

This is what I have.... there seems to be a lot of ScrollRows!!!!!!!!! I know I pasted the format and scrolled down but do I need all this..?! Do I need to

Please help..

dugong


Sub Colour_column()
'
' Colour_columnMacro
' Macro recorded 03/11/2006 by User Name
'
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(D2)=15"
Selection.FormatConditions(1).Font.ColorIndex = 3
Selection.Copy
Range("D3:D2853").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWindow.ScrollRow = 2805
ActiveWindow.ScrollRow = 2802
ActiveWindow.ScrollRow = 2798
ActiveWindow.ScrollRow = 2791
ActiveWindow.ScrollRow = 2787
ActiveWindow.ScrollRow = 2780
ActiveWindow.ScrollRow = 2769
ActiveWindow.ScrollRow = 2758
ActiveWindow.ScrollRow = 2739
ActiveWindow.ScrollRow = 2717
ActiveWindow.ScrollRow = 2695
ActiveWindow.ScrollRow = 2666
ActiveWindow.ScrollRow = 2629
ActiveWindow.ScrollRow = 2593
ActiveWindow.ScrollRow = 2549
ActiveWindow.ScrollRow = 2501
ActiveWindow.ScrollRow = 2450
ActiveWindow.ScrollRow = 2391
ActiveWindow.ScrollRow = 2329
ActiveWindow.ScrollRow = 2278
ActiveWindow.ScrollRow = 2201
ActiveWindow.ScrollRow = 2131
ActiveWindow.ScrollRow = 2072
ActiveWindow.ScrollRow = 1999
ActiveWindow.ScrollRow = 1937
ActiveWindow.ScrollRow = 1867
ActiveWindow.ScrollRow = 1820
ActiveWindow.ScrollRow = 1765
ActiveWindow.ScrollRow = 1706
ActiveWindow.ScrollRow = 1655
ActiveWindow.ScrollRow = 1603
ActiveWindow.ScrollRow = 1556
ActiveWindow.ScrollRow = 1508
ActiveWindow.ScrollRow = 1460
ActiveWindow.ScrollRow = 1409
ActiveWindow.ScrollRow = 1362
ActiveWindow.ScrollRow = 1314
ActiveWindow.ScrollRow = 1270
ActiveWindow.ScrollRow = 1230
ActiveWindow.ScrollRow = 1182
ActiveWindow.ScrollRow = 1142
ActiveWindow.ScrollRow = 1101
ActiveWindow.ScrollRow = 1072
ActiveWindow.ScrollRow = 1039
ActiveWindow.ScrollRow = 1010
ActiveWindow.ScrollRow = 984
ActiveWindow.ScrollRow = 955
ActiveWindow.ScrollRow = 929
ActiveWindow.ScrollRow = 903
ActiveWindow.ScrollRow = 889
ActiveWindow.ScrollRow = 863
ActiveWindow.ScrollRow = 845
ActiveWindow.ScrollRow = 834
ActiveWindow.ScrollRow = 812
ActiveWindow.ScrollRow = 797
ActiveWindow.ScrollRow = 783
ActiveWindow.ScrollRow = 768
ActiveWindow.ScrollRow = 753
ActiveWindow.ScrollRow = 739
ActiveWindow.ScrollRow = 728
ActiveWindow.ScrollRow = 717
ActiveWindow.ScrollRow = 702
ActiveWindow.ScrollRow = 691
ActiveWindow.ScrollRow = 676
ActiveWindow.ScrollRow = 665
ActiveWindow.ScrollRow = 651
ActiveWindow.ScrollRow = 636
ActiveWindow.ScrollRow = 621
ActiveWindow.ScrollRow = 607
ActiveWindow.ScrollRow = 588
ActiveWindow.ScrollRow = 574
ActiveWindow.ScrollRow = 555
ActiveWindow.ScrollRow = 541
ActiveWindow.ScrollRow = 526
ActiveWindow.ScrollRow = 508
ActiveWindow.ScrollRow = 493
ActiveWindow.ScrollRow = 478
ActiveWindow.ScrollRow = 464
ActiveWindow.ScrollRow = 453
ActiveWindow.ScrollRow = 438
ActiveWindow.ScrollRow = 423
ActiveWindow.ScrollRow = 412
ActiveWindow.ScrollRow = 398
ActiveWindow.ScrollRow = 383
ActiveWindow.ScrollRow = 372
ActiveWindow.ScrollRow = 354
ActiveWindow.ScrollRow = 343
ActiveWindow.ScrollRow = 328
ActiveWindow.ScrollRow = 317
ActiveWindow.ScrollRow = 302
ActiveWindow.ScrollRow = 288
ActiveWindow.ScrollRow = 277
ActiveWindow.ScrollRow = 262
ActiveWindow.ScrollRow = 251
ActiveWindow.ScrollRow = 237
ActiveWindow.ScrollRow = 226
ActiveWindow.ScrollRow = 215
ActiveWindow.ScrollRow = 200
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 178
ActiveWindow.ScrollRow = 167
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 149
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 130
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 112
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 90
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 2
End Sub

I am also just starting on VBA in Excel.

My advice is try altering your macro and see what happens.
About the worst that might happen is that your computer will lock solid and will nead re-booting.

Probably a good idea to see if you can work out (at least in vauge general terms) what each command in the macro is doing from the VBA Help facility.
The following is such an entry:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ScrollRow Property

Returns or sets the number of the row that appears at the top of the pane or window. Read/write Long.

Remarks
If the window is split, the ScrollRow property of the Window object refers to the upper-left pane. If the panes are frozen, the ScrollRow property of the Window object excludes the frozen areas.

Example
This example moves row ten to the top of the window.

Worksheets("Sheet1").Activate
ActiveWindow.ScrollRow = 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
So try deleting all but the last of the ScrollRow commands, I suspect that you should find the macro works exactly as before.

Mark.

Thank you both for replying.

Can I be cheeky and ask another thing..?!

If I wanted to change the macro to do a pop-up box asking which column I wanted to apply the format to, how do I go about that? I don't know how to do it!

(p.s.: I am going to the library this weekend to get a couple of books - any you recommend that are good for a learner?)

What you want are "dialog boxes".

Try typing "InputBox Function" into the VBA Help Search box.
Remember the VBA Help is separate from the Excel Help and should be accessed from the VBA Editor.

I've never used one myself, but how hard can it be?

Sorry can't help with any recomendations on books.

Mark