¸Û¨D«üÂI Get Cell Color ¤½¦¡ marco
- ©«¤l
- 37
- ¥DÃD
- 10
- ºëµØ
- 0
- ¿n¤À
- 63
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- 2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-8-25
- ³Ì«áµn¿ý
- 2016-5-24
|
¸Û¨D«üÂI Get Cell Color ¤½¦¡ marco
|
|
|
|
|
|
- ©«¤l
- 37
- ¥DÃD
- 10
- ºëµØ
- 0
- ¿n¤À
- 63
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- 2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-8-25
- ³Ì«áµn¿ý
- 2016-5-24
|
¦^´_ 2# GBKEE - '---------------------------------------------------------------------
- ' ColorIndex Function
- '---------------------------------------------------------------------
- ' Function: Returns the colorindex of the supplied range
- ' Synopsis: Initially, gets a colorindex value for black and white
- ' from the activeworkbook colour palette
- ' Then works through each cell in the supplied range and
- ' determines the colorindex, and adds to array
- ' Finishes by returning acumulated array
- ' Variations: Determines cell colour (interior) or text colour (font)
- ' Default is cell colour
- ' Constraints: Does not count colours set by conditional formatting
- '---------------------------------------------------------------------
- Function ColorIndex(rng As Range, _ Optional text As Boolean = False) As Variant
- '---------------------------------------------------------------------
- Dim cell As Range, row As Range
- Dim i As Long, j As Long
- Dim iWhite As Long, iBlack As Long
- Dim aryColours As Variant
- If rng.Areas.Count > 1 Then
- ColorIndex = CVErr(xlErrValue)
- Exit Function
- End If
- iWhite = WhiteColorindex(rng.Worksheet.Parent)
- iBlack = BlackColorindex(rng.Worksheet.Parent)
- If rng.Cells.Count = 1 Then
- If text Then
- aryColours = DecodeColorIndex(rng, True, iBlack)
- Else
- aryColours = DecodeColorIndex(rng, False, iWhite)
- End If
- Else
- aryColours = rng.Value
- i = 0
- For Each row In rng.Rows
- i = i + 1
- j = 0
- For Each cell In row.Cells
- j = j + 1
- If text Then
- aryColours(i, j) = _
- DecodeColorIndex(cell, True, iBlack)
- Else
- aryColours(i, j) = _
- DecodeColorIndex(cell, False, iWhite)
- End If
- Next cell
- Next row
- End If
- ColorIndex = aryColours
- End Function
- '---------------------------------------------------------------------
- Private Function WhiteColorindex(oWB As Workbook)
- '---------------------------------------------------------------------
- Dim iPalette As Long
- WhiteColorindex = 0
- For iPalette = 1 To 56
- If oWB.Colors(iPalette) = &HFFFFFF Then
- WhiteColorindex = iPalette
- Exit Function
- End If
- Next iPalette
- End Function
- '---------------------------------------------------------------------
- Private Function BlackColorindex(oWB As Workbook)
- '---------------------------------------------------------------------
- Dim iPalette As Long
- BlackColorindex = 0
- For iPalette = 1 To 56
- If oWB.Colors(iPalette) = &H0 Then
- BlackColorindex = iPalette
- Exit Function
- End If
- Next iPalette
- End Function
- '---------------------------------------------------------------------
- Private Function DecodeColorIndex(rng As Range, _ text As Boolean, _ idx As Long)
- '---------------------------------------------------------------------
- Dim iColor As Long
- If text Then
- iColor = rng.Font.ColorIndex
- Else
- iColor = rng.Interior.ColorIndex
- End If
- If iColor < 0 Then
- iColor = idx
- End If
- DecodeColorIndex = iColor
- End Function
- '---------------------------------------------------------------------
- ' End of ColorIndex Function
- '---------------------------------------------------------------------
½Æ»s¥N½X
cellcolor.rar (16.52 KB)
G¤j, §Ú¹ïmacro»yªk¯u¬O¦³¤½¥q, ¦ý¬O°ÝÃD§K±j¸Ñ¨M±o¨ì, ¤£¹L¥t¤@Ó°ÝÃD¤S¨Ó...........
¬°¤°»ò excel ½d³ò·|¦³¨î
=SUMPRODUCT(--(ColorIndex(A1:A5462)=3))
½d³ò A1:A5461 ´N work
½d³ò A1:A5462 ©Î¥H¤W ´N¥Î¤£¨ì ( °ÝÃD¥X¦b¨º ?) |
|
|
|
|
|
|
- ©«¤l
- 37
- ¥DÃD
- 10
- ºëµØ
- 0
- ¿n¤À
- 63
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- 2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-8-25
- ³Ì«áµn¿ý
- 2016-5-24
|
¦^´_ 4# GBKEE - Sub PopulateRangeWithArray()
- Dim x
- ReDim x(1 To 2, 1 To 2)
- x(1, 1) = String(2000, "a"): x(1, 2) = String(5000, "b")
- x(2, 1) = String(17000, "c"): x(2, 2) = String(33000, "d")
- MsgBox Len(x(1, 1)) & "," & Len(x(1, 2)) _
- & "," & Len(x(2, 1)) & "," & Len(x(2, 2))
- Range("a1").Value = x(1, 1)
- Range("b1").Value = x(1, 2)
- Range("a2").Value = x(2, 1)
- Range("b2").Value = x(2, 2)
- End Sub
½Æ»s¥N½X §Ú¤w¸gª¾¹D °}¦C¤¤¤¸¯Àªº¼Æ¥Ø¤W¬O 5461, http://support.microsoft.com/kb/177991
¦ý¬O§Ú¤£ª¾¹D¥H¤W»y¥y«ç¼Ëקï¤Î¥[´¡
cellcolor.rar (16.52 KB)
|
|
|
|
|
|
|