Get number of last colored column

[steve87bg]

Hi,

Im trying to create macro that will find last colored column (any color) in header , and add that column number in some variable x.

Can someone help me do this?

Thanks :help :content:

[Kenneth Hobs]

Depends on what you mean by colored column. Do you mean interior color fill or a conditional format fill? The first is fairly easy.

[steve87bg]

Hi Hobs, I'm mean on interior color.

[steve87bg]

I found solution :)


Sub Count_Colour()
Dim n As Long
Dim cell As Range


For Each cell In ActiveSheet.Range("A1:Z10")
If cell.Interior.ColorIndex <> xlNone Then n = n + 1
Next cell


MsgBox n & " cells have colour"


End Sub

[D_Marcel]

Hum, actually you want to count the number of colored columns instead of find the number of last colored column, right?
I was about to reply and send this code I made specially for you:

Option Explicit
Sub Find_Last_Colored_Column()


Dim i As Integer
Dim Used_Columns As Integer
Dim My_Sheet As Worksheet


Set My_Sheet = Sheets("Plan1")
Used_Columns = My_Sheet.Cells(1, Columns.Count).End(xlToLeft).Column
For i = Used_Columns To 0 Step -1
If Not Cells(1, i).Interior.ColorIndex = xlNone Then
MsgBox "The last colored column is the column " & i & ".", vbInformation
Exit Sub
End If
Next i
End Sub

This code will give you the number of the last colored column. I'm sure that Kenneth would solve in two lines but it works also.

And be welcome to VBAX!

Kind regards,

Douglas

[SamT]

@ Douglas,
You learn quick! That is the same Code Structure I was going to suggest.


Function VBAX_SamT_LastColoredColumn() As Long
'Returns the number of the last Column in Row 1 that is colored with
'the Interior.ColorIndex.Property, (Format Cells >> Fill.)
'Returns -1 if no colored cells found
'For help See: http://www.vbaexpress.com/forum/showthread.php?54076

Dim c As Long

With Sheets("Plan1")
For c = .Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1
If .Cells(1, c).Interior.ColorIndex < 1 Then
VBAX_SamT_LastColoredColumn = c
Exit Function
End If
Next c
End With
VBAX_SamT_LastColoredColumn = -1
End Function


Sub Test_VBAX_SamT_LastColoredColumn()
Dim X
X = VBAX_SamT_LastColoredColumn
End Sub

[D_Marcel]

Thanks SamT!
I'm just following your recomendation:

14628

:biggrin:

[SamT]

I edited my previous code.

@ Douglas: Did you see the possibly fatal error you made? It's in your For...To line.

[D_Marcel]

There is not "zero column", so I shouldn't have set 0 as limit, yes 1. Am I right?

[SamT]

:)

[steve87bg]

Thanks Sam. I took your code. This is more better :)