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
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
Depends on what you mean by colored column. Do you mean interior color fill or a conditional format fill? The first is fairly easy.
Hi Hobs, I'm mean on interior color.
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
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:
[VBA]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[/VBA]
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
"The only good is knowledge and the only evil is ignorance". Socrates
@ 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 FunctionSub Test_VBAX_SamT_LastColoredColumn() Dim X X = VBAX_SamT_LastColoredColumn End Sub
Last edited by SamT; 10-22-2015 at 06:19 PM.
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
Thanks SamT!
I'm just following your recomendation:
SamT.jpg
"The only good is knowledge and the only evil is ignorance". Socrates
I edited my previous code.
@ Douglas: Did you see the possibly fatal error you made? It's in your For...To line.
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
There is not "zero column", so I shouldn't have set 0 as limit, yes 1. Am I right?
"The only good is knowledge and the only evil is ignorance". Socrates
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
Thanks Sam. I took your code. This is more better