finding cell color in array loop

TrippyTom

Well-known Member
Joined
Nov 16, 2004
Messages
587
Pardon my ignorance; I'm still learning.

I have data (calendar days) in an array (7 columns, 6 rows) in VB. I'm using a conditional formatting formula on the range to color the cell red if it's a holiday.

Is it possible to loop through the array to check if the day is colored red?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

TrippyTom

Well-known Member
Joined
Nov 16, 2004
Messages
587
hmm.. when I tried just calling the color index in a cell, pointing at the May 30 cell (which is colored), it didn't work. I'm not sure why. This is what I put in a cell to test it:
Code:
=ColorOfCF($N$19,FALSE)
It returned the "#NAME?" error

My conditional formula for May is set as "FORMULA IS":
Code:
=ISNUMBER(MATCH(M15,$AE$6,0))
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,021
If you're getting the #NAME? error that means that you don't have the UDF defined. You need to scroll down Chip's page there and copy and paste his VBA code into a standard module in your workbook before you can use them in cell formulas.
 

TrippyTom

Well-known Member
Joined
Nov 16, 2004
Messages
587

ADVERTISEMENT

I copied them all in the worksheet I want to run it in. It looks like they all came in fine, each separated by a line.

[edit] .. Nevermind! :) It went into a class module instead of a normal one. I didn't notice that. Now it's reporting some silly long number like "16777215"... is that normal?
 

TrippyTom

Well-known Member
Joined
Nov 16, 2004
Messages
587
Well, I'm using the ColorIndexOfCF command instead.. I think this will work perfectly, once I understand how to code what I'm trying to do.

Thanks for your help and pointing me in the right direction. :)
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,021

ADVERTISEMENT

TrippyTom said:
Well, I'm using the ColorIndexOfCF command instead..

Yeah, I meant to mention that in my post and forgot. ColorIndex is probably more helpful. Color is a long integer that corresponds to the RGB value of the color itself. Normally it's a lot harder to utilize Color than ColorIndex.
 

TrippyTom

Well-known Member
Joined
Nov 16, 2004
Messages
587
this is strange...

If I apply this command to a manually colored cell, it returns the proper color index.

BUT, if I apply it to my conditional formatted cell, it always returns the value "-4142" no matter what color it is. Am I doing something wrong?
 

TrippyTom

Well-known Member
Joined
Nov 16, 2004
Messages
587
ok... I decided to work around this issue. Here's my code so far. It's probably a joke in the eyes of a real coder, but at least I'm trying. It's the only way I'm gonna learn.

Code:
Sub payday()
'MAY
Dim MayArray As Variant

MayArray = Worksheets("Calendar").Range("MAY")

   For i = LBound(MayArray) To UBound(MayArray)
        
      If cell.Value = 15 Then
            
         If ColorIndexOfCF(i, False) = -4142 Then 'cell already has a color in it (holiday)
            If i - 1 = MayArray(, 7) Then '(saturday)
               payday = i - 2
               Cells.Interior.ColorIndex = 4 'color cell green
            If i - 1 = MayArray(, 1) Then '(sunday)
               payday = i - 3
               Cells.Interior.ColorIndex = 4 'color cell green
            Else '(mon-fri)
               payday = i - 1
               Cells.Interior.ColorIndex = 4 'color cell green
            End If
         ElseIf i - 1 = MayArray(, 7) Then '(saturday)
            payday = i - 2
            Cells.Interior.ColorIndex = 4 'color cell green
         ElseIf i - 1 = MayArray(, 1) Then '(sunday)
            payday = i - 3
            Cells.Interior.ColorIndex = 4 'color cell green
         Else '(the day must be on a non-holiday weekday so color normally)
            Cells.Interior.ColorIndex = 4 'color cell green
         End If
        
      ElseIf cell.Value = 30 Then
        
         If ColorIndexOfCF(i, False) = -4142 Then 'cell already has a color in it (holiday)
            If i - 1 = MayArray(, 7) Then '(saturday)
               payday = i - 2
               Cells.Interior.ColorIndex = 4 'color cell green
            If i - 1 = MayArray(, 1) Then '(sunday)
               payday = i - 3
               Cells.Interior.ColorIndex = 4 'color cell green
            Else '(mon-fri)
               payday = i - 1
               Cells.Interior.ColorIndex = 4 'color cell green
            End If
         ElseIf i - 1 = MayArray(, 7) Then '(saturday)
            payday = i - 2
            Cells.Interior.ColorIndex = 4 'color cell green
         ElseIf i - 1 = MayArray(, 1) Then '(sunday)
            payday = i - 3
            Cells.Interior.ColorIndex = 4 'color cell green
         Else '(the day must be on a non-holiday weekday so color normally)
            Cells.Interior.ColorIndex = 4 'color cell green
         End If
        
      End If
      
   Next i
   
End Sub
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,021
Tom,

I had used Chip's CF functions before to solve my own problem and everything worked fine. However, I tried using them under the conditions you describe and they mis-fired in a couple of places. I've re-written the code to try and fix that, but want to see if I can communicate with Chip himself before "going public" with the changed code.

Will post back later.
 

Forum statistics

Threads
1,148,055
Messages
5,744,539
Members
423,882
Latest member
Seeham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top