PDA

View Full Version : [SOLVED] Get number of last colored column



steve87bg
10-22-2015, 02:00 PM
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
10-22-2015, 02:16 PM
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
10-22-2015, 02:37 PM
Hi Hobs, I'm mean on interior color.

steve87bg
10-22-2015, 03:44 PM
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
10-22-2015, 03:55 PM
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
10-22-2015, 04:44 PM
@ 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
10-22-2015, 06:00 PM
Thanks SamT!
I'm just following your recomendation:

14628

:biggrin:

SamT
10-22-2015, 06:22 PM
I edited my previous code.

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

D_Marcel
10-22-2015, 06:43 PM
There is not "zero column", so I shouldn't have set 0 as limit, yes 1. Am I right?

SamT
10-22-2015, 08:38 PM
:)

steve87bg
10-24-2015, 01:08 PM
Thanks Sam. I took your code. This is more better :)