PDA

View Full Version : [SOLVED] Interior.ColorIndex



crmpicco
05-26-2005, 06:40 AM
iArrayContentColour = Mid(gaContentColour(j), iPos + 1, Len(gaContentColour(j)) - 1)




is it possible to test a whole sheet to find out if any cells are coloured with the ColorIndex of the value of iArrayContentColour (which is 35)

Norie
05-26-2005, 07:04 AM
Check this link Functions For Working With Cell Colors (http://www.cpearson.com/excel/colors.htm)

crmpicco
05-26-2005, 07:06 AM
something like this, but how do i get the NUMBER of cells with ColorIndex = 35?


Getting The Range Of Cells With A Specific Color

The following function will return a Range object consisting of those cells in a range that have either an Interior (background) or Font of a specified color. InRange is the range of cells to examine, WhatColorIndex is the ColorIndex value to count, and OfText indicates whether to return the ColorIndex of the Font (if True) or the Interior (if False). This function uses the AddRange function to combine two ranges into a single range, without the possible problems of the Application.Union method. See AddRange, below, for more details about this function.

Function RangeOfColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Range
'
' This function returns a Range of cells in InRange with a
' background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
If (Rng.Font.ColorIndex = WhatColorIndex) = True Then
Set RangeOfColor = AddRange(RangeOfColor, Rng)
End If
Else
If (Rng.Interior.ColorIndex = WhatColorIndex) = True Then
Set RangeOfColor = AddRange(RangeOfColor, Rng)
End If
End If
Next Rng

End Function

crmpicco
05-26-2005, 07:23 AM
i kind of need it to give me a NUMBER / count to go on...

johnske
05-26-2005, 07:28 AM
Try these (set your own range)


Sub ColourIndex35Count()
'counts the number of "35"s
Dim Cell As Range, N&
Application.ScreenUpdating = False
N = 0
For Each Cell In Range("A1:500")
If Cell.Interior.ColorIndex = 35 Then
N = N + 1
End If
Next Cell
MsgBox N
End Sub[/vba]Sub ColourIndex35Location()
'finds the location of "35"s
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Range("A1:500")
If Cell.Interior.ColorIndex = 35 Then
MsgBox "Colour index 35 at " & Cell.Address
End If
Next Cell
End Sub[vba]Sub ColourIndex35()
'finds the number AND location of "35"s
Dim Cell As Range, N&
Application.ScreenUpdating = False
N = 0
For Each Cell In Range("A1:500")
If Cell.Interior.ColorIndex = 35 Then
MsgBox "Colour index 35 at " & Cell.Address
N = N + 1
End If
Next Cell
MsgBox N
End Sub

Richie(UK)
05-26-2005, 07:35 AM
i kind of need it to give me a NUMBER / count to go on...
Hi,

Did you look at the information in the link that Norie provided?

There are functions there for both counting and summing based upon colour - they are titled 'CountByColor' and 'SumByColor' ;)

crmpicco
05-26-2005, 08:04 AM
cheers Richie and Johnske, i'm a kinda VBA Excel newbie

crmpicco
05-26-2005, 08:28 AM
i keep getting an error with this syntax:


Dim Cell As Range, N&
Application.ScreenUpdating = False
N = 0
For Each Cell In Range("A1:" & last_column & last_row & ")
If Cell.Interior.ColorIndex = 35 Then
N = N + 1
End If
Next Cell
MsgBox N

crmpicco
05-26-2005, 08:36 AM
Error:
List Seperator required )

crmpicco
05-26-2005, 08:43 AM
fix:


Dim Cell As Range, N&
Application.ScreenUpdating = False
N = 0
For Each Cell In Range("A1:" & getColumnLetter(last_column) & last_row)
If Cell.Interior.ColorIndex = iArrayContentColour Then
N = N + 1
End If
Next Cell

johnske
05-26-2005, 08:53 AM
Hi crmpicco,

I don't know where last_column and last_row came from, but if you've got it fixed - fine. If not, try this


Sub ColourIndexColRow()
Dim Cell As Range, N&
Application.ScreenUpdating = False
N = 0
For Each Cell In ActiveSheet.UsedRange
If Cell.Interior.ColorIndex = 35 Then
N = N + 1
End If
Next Cell
MsgBox N
End Sub

crmpicco
05-26-2005, 09:02 AM
this works, thanks:


'... find the last used row and column ( prevents wasted processing )
last_row = Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).row
last_column = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column

Application.ScreenUpdating = False
N = 0
For Each Cell In Range("A1:" & getColumnLetter(last_column) & last_row)
aaas = "A1:" & getColumnLetter(last_column) & last_row
If Cell.Interior.ColorIndex = 35 Then
N = N + 1
End If
Next Cell

crmpicco
05-26-2005, 09:02 AM
also:


Function getColumnLetter(iColNumber)
Dim iOnes As Integer
Dim iTens As Integer
Dim sCol As String
iOnes = iColNumber Mod 27
iTens = iColNumber \ 27
If iOnes = 0 Then
iOnes = 1
End If
If iTens = 0 And iOnes <> 0 Then
sCol = Chr$(iOnes + 64)
Else
sCol = Chr$(iTens + 64) & Chr$(iOnes + 64)
End If
getColumnLetter = sCol
End Function

johnske
05-26-2005, 09:15 AM
this works, thanks:


'... find the last used row and column ( prevents wasted processing )
last_row = Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).row
last_column = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column

Application.ScreenUpdating = False
N = 0
For Each Cell In Range("A1:" & getColumnLetter(last_column) & last_row)
aaas = "A1:" & getColumnLetter(last_column) & last_row
If Cell.Interior.ColorIndex = 35 Then
N = N + 1
End If
Next Cell


OK, but just so you avoid any errors - last_row and last_column only gives the last row and column with values in them. If you have some coloured cells outside of those, they won't be counted.

The method I gave uses the "used range", and the used range also includes all empty cells that may be coloured.

However if what you have gives the desired results - fine.

regards,
John :thumb

Cyberdude
05-26-2005, 11:42 AM
crmpicco sure spells "color" funny. :rotlaugh:

Norie
05-26-2005, 01:05 PM
crmpicco sure spells "color" funny. :rotlaugh:
What correctly?:yes

crmpicco
05-27-2005, 02:33 AM
Sub ColourIndex35Count()
'counts the number of "35"s
Dim Cell As Range, N&
Application.ScreenUpdating = False
N = 0
For Each Cell In Range("A1:500")
If Cell.Interior.ColorIndex = 35 Then
N = N + 1
End If
Next Cell
MsgBox N
End Sub


Along the same lines as the code above is there a way to count the number of SHEETS that n <> 0

crmpicco
05-27-2005, 03:42 AM
or there are no '35's in that sheet

Bob Phillips
05-27-2005, 05:00 AM
or there are no '35's in that sheet

I would use a Colorindex function, then use worksheet formulae to count the instances of colour, or not, and across sheets.

crmpicco
05-27-2005, 05:05 AM
how can i do that similar to this code:


n = 0
For Each Cell In Range("A1:" & getColumnLetter(last_column) & last_row)
aaas = "A1:" & getColumnLetter(last_column) & last_row
If Cell.Interior.ColorIndex = 35 Then
n = n + 1
End If
Next Cell
If n <> 0 Then
del_content_range = True
End If

crmpicco
05-27-2005, 07:21 AM
Dim Cell As Range, N&
what is the N& doing here?

Bob Phillips
05-27-2005, 07:23 AM
Dim Cell As Range, N&

what is the N& doing here?

It is shorthand notation for typing the variable as a long.

Don't like it myself, it just seems to be a throwback to old Basics, saves so little typing and makes it less readable (and prompts questions like this :)).

And why would you use a explicit typing for one variable (cell), and shorthand for the other? Why, because Range doesn't have a shorthand, the old Basics didn't deal with Ranges - yuuk!

crmpicco
05-27-2005, 07:25 AM
yes, i agree