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?

Macro
 

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

Macro
 

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