Macro

I have a macro to change the colour of a column depending on the contents of
one of the cells in that column.
Sub Column_Colour()
ActiveCell.Select
If ActiveCell = "Allocated" Then
Range("H1:H100").Interior.ColorIndex = 36
ElseIf ActiveCell = "In Use" Then
Range("H1:H100").Interior.ColorIndex = 37
ElseIf ActiveCell = "Returned" Then
Range("H1:H100").Interior.ColorIndex = 43
End If
ActiveSheet.Range("A5").Activate

End Sub

As you can see this is specific only to column "H". How can I change this
macro to be available to any column that is selected?

You can try something like:

Sub Column_Colour()
col = Mid(CStr(ActiveCell.Address), 2, 1)

If ActiveCell = "Allocated" Then

Range(col & "1:" & col & "100").Interior.ColorIndex = 36
ElseIf ActiveCell = "In Use" Then
Range(col & "1:" & col & "100").Interior.ColorIndex = 37
ElseIf ActiveCell = "Returned" Then
Range(col & "1:" & col & "100").Interior.ColorIndex = 43
End If
ActiveSheet.Range("A5").Activate

End Sub


Regards,
Robert

Works exactly as I want. Many thanks

"cmart02" wrote:

> You can try something like:
>
> Sub Column_Colour()
> col = Mid(CStr(ActiveCell.Address), 2, 1)
>
> If ActiveCell = "Allocated" Then
>
> Range(col & "1:" & col & "100").Interior.ColorIndex = 36
> ElseIf ActiveCell = "In Use" Then
> Range(col & "1:" & col & "100").Interior.ColorIndex = 37
> ElseIf ActiveCell = "Returned" Then
> Range(col & "1:" & col & "100").Interior.ColorIndex = 43
> End If
> ActiveSheet.Range("A5").Activate
>
> End Sub
>
>
> Regards,
> Robert