Need VBA for referencing the intersection of the current (that is, "current" while in a loop) selected Cell's Column with a named range

Hi Experts,

I'm trying to adjust the code below provided by Mark Sloboda but keep getting a  "Compile Error: Type Mismatch" on the line (between the asterisked statements below) :

 If Intersect(myCell.Column, Range("messeRange")) = "Y"

... which has the ".column" highlighted when the error message is displayed.

I'm basically wanting to just change the original code to add another loop through the selected range if there is a "Y" in the Named Range "messe" (which is defined as the range "AR7:KA7" on the active sheet (Sheet1).

Hope someone can see where I am going wrong.

Thanks,

Jeff




Sub LoopAndChangeColorForThisRange()

'http://www.ozgrid.com/forum/showthread.php?t=68172
'BROUGHT TO YOU BY WWW.PROGRAMMINGLIBRARY.COM
'CREATED BY MARK SLOBODA

'    OptimizeCode_Begin

'************************* DEC VARS *******************************
    Dim myCell As Range
    Dim myRng As Range
    Dim FirstAddress As String
    Dim iCtr As Long
    Dim letCtr As Long
    Dim startrow As Long    'BEGINNING OF RANGE
    Dim endrow As Long    ' END OF RANGE
    Dim startcolumn As Integer    'BEGINNING COLUMN
    Dim endcolumn As Integer    'END COLUMN
    Dim accomodationLetters
    Dim messeRange As Range     'JWS
    '************************* SET VALUES*****************************
    'DUMMY VALUES - COULD BE PASSED
    'Set targetRange = ThisWorkbook.Sheets(1).Range("AR9:KA509")

    startrow = 10
    endrow = 509
    startcolumn = 44
    endcolumn = 287

    'SET UP RANGE YOU ARE COLORING AND BOLDING -YOU COULD MODIFY TO PASS VALUE TO
    Set myRng = Range(Cells(startrow, startcolumn), Cells(endrow, endcolumn))

    'SET UP messe RANGE FOR ALTERNATE FONT COLOR   JWS

    Set messeRange = ThisWorkbook.Names("messe").RefersToRange    'JWS

    'SET UP ARRAY WITH WORDS YOU WANT TO COLOR AND BOLD - YOU COULD PUSH VALUES FROM A LISTBOX TO THIS ARRAY
    accomodationLetters = Array("D", "J", "P", "S")

    'BEGIN MASTER LOOP---------------------------------------

    If Range("AN" & ActiveCell.Row()) <> "" Then

        For iCtr = LBound(accomodationLetters) To UBound(accomodationLetters)
            'ERROR FOUND-BYPASS
            On Error Resume Next

            With myRng
                Set myCell = .Find(What:=accomodationLetters(iCtr), After:=.Cells(1), _
                                   LookIn:=xlValues, LookAt:=xlPart, _
                                   SearchOrder:=xlByRows, _
                                   SearchDirection:=xlNext, _
                                   MatchCase:=False)
                'LOGIC CHECK
                If Not myCell Is Nothing Then
                    FirstAddress = myCell.Address

                    Do
                        For letCtr = 1 To Len(myCell.Value)
                            If StrComp(Mid(myCell.Value, letCtr, _
                                           Len(accomodationLetters(iCtr))), _
                                           accomodationLetters(iCtr), vbTextCompare) = 0 Then
                                myCell.Characters(Start:=letCtr, _
                                                  Length:=Len(accomodationLetters(iCtr))) _
                                                  .Font.Color = RGB(255, 255, 255)   '.Font.ColorIndex = 5

' ******** Type Mismatch on '.Column' in next line *********

                                If Intersect(myCell.Column, Range("messeRange")) = "Y" Then  'JWS
                                    myCell.Characters(Start:=letCtr, _
                                                      Length:=Len(accomodationLetters(iCtr))) _
                                                      .Font.Color = RGB(196, 215, 155)    'medium green     JWS
                                End If

'********* End of my modifications *********
                            
                            End If

                            DoEvents

                        Next letCtr

                        'GET NEXT ADDRESS
                        Set myCell = .FindNext(myCell)

                    Loop While Not myCell Is Nothing _
                         And myCell.Address <> FirstAddress
                End If
            End With

            DoEvents

        Next iCtr

    End If

    Set myRng = Nothing
    Set myCell = Nothing

    '    OptimizeCode_End

End Sub

Open in new window

LVL 2
Jeffrey SmithOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Can you give more details here about what is that you want to achieve here?? And what you are trying to do???
Jeffrey SmithOwnerAuthor Commented:
Hi Saurabh,

Thanks for posting on my issue - yet again ;-)

The code before I added the extra IF|Then statement served to make the Characters D, J, P & S appear as white font so that they would become invisible with a White fill color.  However, in some cases, the whole Column's fill color was changed (via Conditional Formatting triggered if the Value in cells AR7:KA7 have a value of "Y") to become a shade of green.  Therefore, those now White characters became visible against the now green background.  Therefore, I am trying (with my additional IF|Then within the loop and surrounded by the asterisked lines), change the font to match the shade of green (and therefore, still stay "invisible").

Hope that's clear.

Jeff
Saurabh Singh TeotiaCommented:
Jeff,

I have a simpler solution for you..Now you said it applies a conditional formatting and the line color becomes green if the condition is meet.. Now in the same condition you can also select the font of the cell or color..So you can set the font as well in the same conditional formatting which will do what you are looking for so you don't have to check in the code...You can keep it simple by defining the font color in the conditional formatting itself..

Saurabh...
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Saurabh Singh TeotiaCommented:
Again if you still want to use this way then..

Replace this line...

 If Intersect(myCell.Column, Range("messeRange")) = "Y" Then  'JWS
                                    myCell.Characters(Start:=letCtr, _
                                                      Length:=Len(accomodationLetters(iCtr))) _
                                                      .Font.Color = RGB(196, 215, 155)    'medium green     JWS
                                End If

Open in new window


With This...

    If Not Intersect(myCell.Column, Range("messeRange")) Is Nothing Then    'JWS
        If UCase(myCell.Value) = "Y" Then myCell.Characters(Start:=letCtr, Length:=Len(accomodationLetters(iCtr))).Font.Color = RGB(196, 215, 155)       'medium green     JWS
    End If

Open in new window


Saurabh...
Jeffrey SmithOwnerAuthor Commented:
Hi Saurabh,

I tried replacing the code as you suggested (just so I could learn what was wrong with mine) but it gave me the same Type Mismatch error.

I'm on to try doing this with CF.

Jeff
Jeffrey SmithOwnerAuthor Commented:
So, I'm looking at the CF possibilities and I think I can only Color the Font for the entire cell, not only the Characters  D, J, P & S ...

CF can only evaluate to True/False, and I don't think I know how to construct an Excel formula that does everything I'm wanting that macro to do in T/F terms ...

Jeff
Saurabh Singh TeotiaCommented:
Does it gives you error on this one too??

If Not Intersect(myCell, Range("messeRange")) Is Nothing Then
Jeffrey SmithOwnerAuthor Commented:
No errors now (thanks!), but it did not turn the Characters  D, J, P & S into a green font.  They still stay white. Can you see where this is going wrong?
Saurabh Singh TeotiaCommented:
You need to change this line...

If UCase(myCell.Value) = "Y" Then myCell.Characters(Start:=letCtr, Length:=Len(accomodationLetters(iCtr))).Font.Color = RGB(196, 215, 155) 

Open in new window


To this...

If UCase(myCell.Value) = "Y" or UCase(myCell.Value) = "D" OR UCase(myCell.Value) = "J"  OR UCase(myCell.Value) = "P" OR UCase(myCell.Value) = "S"  Then myCell.Characters(Start:=letCtr, Length:=Len(accomodationLetters(iCtr))).Font.Color = RGB(196, 215, 155) 

Open in new window


Saurabh...
Jeffrey SmithOwnerAuthor Commented:
Hmm ... I tried a few things:

1.

Your last suggestion:
 If Not Intersect(myCell, Range("messeRange")) Is Nothing Then     'JWS

                     If UCase(myCell.Value) = "Y" Or UCase(myCell.Value) = "D" Or UCase(myCell.Value) = "J" Or UCase(myCell.Value) = "P" Or UCase(myCell.Value) = "S" Then myCell.Characters(Start:=letCtr, Length:=Len(accomodationLetters(iCtr))).Font.Color = RGB(196, 215, 155)

Open in new window


... turns the letters all White, regardless of whether there is  a "Y" in the "messe" range on Row 7, so it also is doing it in the Columns with no "Y" in Row 7 (that have a Green fill color, so those D, J, P & S characters still show up.

2.

This variation on your suggestion (I took out the MyCell.Value = "Y" test because "Y" is the "trigger" that if present in Row 7, is supposed to activate the rest of the code that changes the Font Color for the characters D, J, P & S in the myCell Range (and the messe range therefore is not part of the myCell range):
If Not Intersect(myCell, Range("messeRange")) Is Nothing Then     'JWS

                 If UCase(myCell.Value) = "D" Or UCase(myCell.Value) = "J" Or UCase(myCell.Value) = "P" Or UCase(myCell.Value) = "S" Then myCell.Characters(Start:=letCtr, Length:=Len(accomodationLetters(iCtr))).Font.Color = RGB(196, 215, 155)

Open in new window


... but it does the same thing as Step 1.

3.

I went back and tried some combinations of the earlier code:
 If Intersect(myCell, Range("messeRange")) = "Y" Then     'JWS

                     myCell.Characters(Start:=letCtr, _
                                                      Length:=Len(accomodationLetters(iCtr))) _
                                                      .Font.Color = RGB(196, 215, 155)

Open in new window


... which turns the Font Green in the Columns it's supposed to (that is, the ones with a "Y" in the "messe" range on Row 7 (and a Green Fill Color), but it also is doing it in the Columns with no "Y" in Row 7 (that have a White (or no) fill color, so those D, J, P & S characters are still visible in those latter cells.

4.

This:

 
If Not Intersect(myCell, Range("messeRange")) Is Nothing Then     'JWS

                                    myCell.Characters(Start:=letCtr, _
                                                      Length:=Len(accomodationLetters(iCtr))) _
                                                      .Font.Color = RGB(196, 215, 155)

Open in new window


... did the same thing as Step 3.

I think we are getting close, Saurabh, but I am still not seeing it.

Thanks,

Jeff
Saurabh Singh TeotiaCommented:
Assuming you want to check A Column row-7 then you can use this...

 If Not Intersect(myCell, Range("messeRange")) Is Nothing Then     'JWS

                     If UCase(cells(myCell.row,"a")) = "Y" and (UCase(myCell.Value) = "D" Or UCase(myCell.Value) = "J" Or UCase(myCell.Value) = "P" Or UCase(myCell.Value) = "S" Then myCell.Characters(Start:=letCtr, Length:=Len(accomodationLetters(iCtr))).Font.Color = RGB(196, 215, 155))

Open in new window

Jeffrey SmithOwnerAuthor Commented:
Not sure where you are going with that, Saurabh, but I am getting a Compile Syntax error on the 2nd line.

In particular, I am not understanding this part:

[indent]If UCase(cells(myCell.row,"a")) = "Y"[/indent]

Open in new window


The code is potentially going to run against as many as 244 columns, but the same thing is wanted in each of them:  IF there is a "Y" in Row 7 of that messe Range (AR7:KA7), then I want the Characters D, J, P, & S to appear in Green Font so they become invisible.  The immediately preceding and previously working code already changes those Characters to White Font for ALL Cells in the myCell Range, so this part I'm dealing with here is circling back and attempting to deal with those Columns that have Green FILL Color (and that Green Fill Color is triggered by the "Y" in Row 7 via Conditional Formatting), and change the Font to match the Green Fill color, so that those Characters are invisible regardless of whether the Fill color is White or Green.

Jeff
Saurabh Singh TeotiaCommented:
When you say row 7 has Y.. where exactly you are looking for Y?? Which cell value you are saying will have Y in it??
Jeffrey SmithOwnerAuthor Commented:
I think the problem is with the
Intersect(myCell, Range (messeRange")) = "Y" Then

Open in new window

section.  While I apparently had the syntax wrong in my initially posted code, what I was trying to reference was the "myCell.Column" intersection with the messeRange in Row 7.  As it stands now, myCell is ALWAYS going to be a single cell in the myRng Range, and it will NEVER intersect with Row 7, because they will all be in Rows 10 to 509.  That is why I was trying to see if there was a "Y" in Row 7 of the Column that myCell is in ...

Jeff
Jeffrey SmithOwnerAuthor Commented:
Strangely, even though my originally posted reference was "myCell.Column" being identified as a "Type Mismatch" error, I can put a Breakpoint on the "If Intersect" line and check the Immediate Window with:

?myCell.Column

... and get a (correct) return of:

220

So, why is it giving the error when the code runs?

Jeff
Jeffrey SmithOwnerAuthor Commented:
If this helps, when I change my error-handling option to Break on all Errors, I get a

"Run-time error '1004': Method 'Range' of Object"_Global' failed on this line:

If Intersect(myCell, Range("messeRange")) = "Y" Then

Jeff
Saurabh Singh TeotiaCommented:
Yeah you will get an error if you mycell range doesn't belong to messerange and it will give you an error because of that..
Jeffrey SmithOwnerAuthor Commented:
If it helps to clarify what I'm trying to do here, let me re-state the goal:

For all of the cells in myRng (AR7:KA509), I want any Alpha characters to be invisible (that is, the Font Color should match the Fill Color of the Cell (so only Numeric characters should be visible) - I mention that in case it is easier to achieve than the current approach focusing on the individual letters D, J, P & S which are the only letters that Data Validation will accept).  The Cells will normally have a White Fill Color (and the Alpha characters should therefore have a White Font Color, and that part is working from the earlier code:

For letCtr = 1 To Len(myCell.Value)
                            If StrComp(Mid(myCell.Value, letCtr, _
                                           Len(accomodationLetters(iCtr))), _
                                           accomodationLetters(iCtr), vbTextCompare) = 0 Then
                                myCell.Characters(Start:=letCtr, _
                                                  Length:=Len(accomodationLetters(iCtr))) _
                                                  .Font.Color = RGB(255, 255, 255)

Open in new window


... and the part I am trying to add is that after the above code is run, I need to check for a "Y" returned by the formulas in Row 7 of the column that the Cell currently being evaluated resides in, because that "Y" is what activates Conditional Formatting that turns the Cell Fill Color to Green (and therefore, I need to circle back to change the Font Color to Green if that Y exists in Row 7 of that Cell's Column.  

Hope this is clearer.
Saurabh Singh TeotiaCommented:
Jeff,

Now if you want to hide all the words which are not "Y" THEN any particular reason you don't want to use conditional formatting which will do what you are looking for..

And you can circle back to row 7 assuming that row-7 is of A Column then you can use...

=A7="Y"

that will only activate your conditional formatting if the condition is met..Again can you give me your sample file to work with so that i can see and can provide you a conditional formatting and you can do a conditional formatting on font color as well..
Jeffrey SmithOwnerAuthor Commented:
The thing is, Saurabh, it's not the whole contents of the cells I want to color the font for, it is only the alpha characters of the cell contents I want to hide.  So, I have cells with content like D1, JS2, PS4, S1 ... and I only want the numbers to appear (thus I am trying to make the letters have the same Font Color as the Fill Color, which will always be White, unless their is a "Y' in Row 7 for the Column of the Cell (via Conditional Formatting, the Y activates the Fill Color to be Green, and I need the code then [and only in that circumstance] to make the alpha characters Green to match (there are some 244 columns in myRng that are being evaluated).  Conditional Formatting does not have the ability to color only part of the contents of the cell - it is all or nothing.

I can make up an example file but it will take me some time to do that, so I was hoping to describe what I wanted the code to do instead ...
Saurabh Singh TeotiaCommented:
Jeff,

If i understand correctly you got a range which have alphanumeric characters now you want to hide those characters and show only number one...

I can write this code for you but help me understand couple of things..the range which you want to check is-->messeRange ??

You want to do this activity when all cells from -->AR7:KA7 has "Y" in it or any cell in that range has "Y" in it??

Saurabh...
Jeffrey SmithOwnerAuthor Commented:
Hi Saurabh,

Re:
If i understand correctly you got a range which have alphanumeric characters now you want to hide those characters and show only number one...

Yes, I have a range of cells AR10:KA509 that may contain cells with content like D1, JS2, PS4, S1 and I want to hide the alpha characters so they display as (in this example) as 1, 2, 4, 1.  The code I started with (in the snippet in my ID: 40843923 post) accomplishes this by turning those alpha characters White to match the White background.  However, some columns will have a "Y" in the messeRange (AR7:KA7) which activates a Conditional Formatting Rule and turns the Fill Color Green, thus making the alpha characters White against the Green background and therefore visible.  Fixing that to make the alpha characters Green in those conditions, is what I am struggling with here.

There is preceding code that both hides Columns that don't apply (via code I use in kind of a "Column Filtering" routine) in the AR10:KA509 range and also filters the AR10:KA509 range to only rows meeting the AutoFilter Criteria, before this "LoopAndChangeColorForThisRange" procedure runs.  Both will serve to reduce the 244 Column, 500 Row range significantly, so this code would run much faster if it only runs against those potentially visible cells in the Rows 10:509 (if they haven't been filtered) and in Columns AR:KA (if they haven't been hidden).


I hope this is clear, Saurabh.
Saurabh Singh TeotiaCommented:
Yeah this gives clarity..about what you are trying to do..can you answer my second question as well which is this...

You want to do this activity when all cells from -->AR7:KA7 has "Y" in it  as in all the cells or any cell in that range has "Y" in it??

Saurabh...
Jeffrey SmithOwnerAuthor Commented:
Only if AR7:KA7 has "Y" in it
Jeffrey SmithOwnerAuthor Commented:
Because the Y in AR7:KA7 is what also triggers the CF and changes the Fill Color to Green
Saurabh Singh TeotiaCommented:
Jeff..

I'm asking which cell will have Y... will it be in cell ar7 or AS7 OR AST7 OR AU7 OR AV7 or the entire range from ar7 to ka7 will have Y mentioned in it??
Jeffrey SmithOwnerAuthor Commented:
Oh, sorry, Saurabh.  Any of the cells in AR7:KA7 could have a "Y" in it.
Jeffrey SmithOwnerAuthor Commented:
Ok, I finally have an example file cleaned up for you to work with, Saurabh.  Sorry, I should have done this much sooner so I wasn't asking you to work in the dark (I had hoped it might be an easy code fix (well, easy for someone with better skills than me anyway).  Unfortunately, the EE Upload function has a glitch (I select the file, it shows the File name I selected, I click Upload, the File name then disappears and it says “No file selected” but the “Uploading …” message keeps blinking on and off but it’s just stuck and nothing happens.  Anyway, I have uploaded it to a Shared Folder (“EE”) on my Dropbox account here:  

https://www.dropbox.com/sh/tqfscfx11q3v3gj/AADFbnYQ4A7XF86J2mO0z2OBa?dl=0

The worksheet is protected at the end of the macro but if you need to Unprotect/Protect there is a macro with a keyboard shortcut of CTRL+U that will toggle back and forth.

In it's present condition, the macro "View_Hotel_Schedule" in Module1 has already been run (and that hides the Columns that don't apply to the Hotel selected (the Hotel is selected in Cell AP3) and filters out the clients that are not staying at the Hotel. The Hotel selected is "Graf Zeppelin" as that one has both dates where "messe" pricing is in effect (that is, with a "Y" in Row 7) as well as days without.  If needed, the "View_Hotel_Schedule" macro can be run from the "Navigator" Button in A1, and then click the "Select Hotel" button from the Userform. That macro will also call the "LoopAndChangeColorForThisRange" macro that we have been working on in this question (the version here does not have any of my modifications to deal with the Coloring of the Font in those cells with the Green background ... as I obviously didn't have it working anyway).

By the way, Saurabh, if you happen to notice any possible changes in my code that might make it run faster, I would appreciate your insights on that, too (it used to run in about 15 seconds or so, but now it's taking probably a minute or more).

Jeff
Jeffrey SmithOwnerAuthor Commented:
P.S.:

The example file also illustrates that the number of unhidden columns and filtered rows is very small compared to the entire myRng Range ... so if the code can be made to run against only those visible cells, that should help it to run much faster, too.
Jeffrey SmithOwnerAuthor Commented:
I was finally able to get the file uploaded via EE so you don't have to use the previous dropbox link.

Jeff

EE-Example.xlsm
Saurabh Singh TeotiaCommented:
In your workbook..Can you tell me what range messe range is? what does it refers to as it's not listed in your workbook..Can you tell me which range it has in the same??
Jeffrey SmithOwnerAuthor Commented:
Sorry, Saurabh.

To provide the EE Example file, I went back to an earlier version that preceded my non-working code that I initially posted this question with (because I didn't want to clutter up the file), and that version did not yet have the "messe" Range defined.  It should be (and is, in my current file) defined as:

='2015'!$AR$7:$KA$7

Jeff
Saurabh Singh TeotiaCommented:
Jeff,

And what is the complete range that you want to check where you want to change the colors of cells??

Saurabh...
Jeffrey SmithOwnerAuthor Commented:
Well, that is the myRng ("AR10:KA509") section.  But as I mentioned above:

The example file also illustrates that the number of unhidden columns and filtered rows is very small compared to the entire myRng Range ... so if the code can be made to run against only those visible cells (in that Range), that should help it to run much faster, too.
Saurabh Singh TeotiaCommented:
Jeff your worksheet is protected what is the password of your worksheet??
Jeffrey SmithOwnerAuthor Commented:
I had commented in my ID: 40846499 post that:

The worksheet is protected at the end of the macro but if you need to Unprotect/Protect there is a macro with a keyboard shortcut of CTRL+U that will toggle back and forth.

... but the pw is svenT!

Jeff
Saurabh Singh TeotiaCommented:
Here you go try this code and it does what you are looking for..

Sub speicfycolor()
    Dim rng As Range
    Dim cell As Range, k As Long
    Dim accomodationLetters
    Dim iCtr As Long

    accomodationLetters = Array("D", "J", "P", "S")


    Set rng = Range("AR10:KA509").SpecialCells(xlCellTypeVisible)

    For Each cell In rng

        If UCase(Trim(Cells(7, cell.Column).Value)) = "Y" Then
            For iCtr = LBound(accomodationLetters) To UBound(accomodationLetters)
                For letCtr = 1 To Len(cell.Value)
                    cell.Characters(Start:=letCtr, Length:=Len(accomodationLetters(iCtr))).Font.Color = RGB(196, 215, 155)
                Next letCtr
            Next iCtr
        End If
    Next cell


End Sub

Open in new window


Saurabh...
Saurabh Singh TeotiaCommented:
Try the code and it will do what you are looking for..and never mind the password since i realized it is true for your current workbook...
Jeffrey SmithOwnerAuthor Commented:
Saurabh,

That is making the numeric characters have a Green Font, too (If there is a Y in Row 7).  So the whole cell contents are invisible against the Green Fill Color, instead of just the alpha characters.

Jeff
Jeffrey SmithOwnerAuthor Commented:
So, these are the (incorrect) results:

So, these are the (incorrect) results:2nd view
Jeff
Saurabh Singh TeotiaCommented:
Jeff,

Their u go fixed that problem...

Sub speicfycolor()
    Dim rng As Range
    Dim cell As Range, k As Long
    Dim accomodationLetters
    Dim iCtr As Long

    accomodationLetters = Array("D", "J", "P", "S")


    Set rng = Range("AR10:KA509").SpecialCells(xlCellTypeVisible)

    For Each cell In rng

        If UCase(Trim(Cells(7, cell.Column).Value)) = "Y" Then
         cell.NumberFormat = "@"
            For iCtr = LBound(accomodationLetters) To UBound(accomodationLetters)
                For letCtr = 1 To Len(cell.Value)
               If UCase(Mid(cell.Value, letCtr, 1)) = Ucase(accomodationLetters(iCtr)) Then cell.Characters(Start:=letCtr, Length:=Len(accomodationLetters(iCtr))).Font.Color = RGB(196, 215, 155)
                Next letCtr
            Next iCtr
        End If
    Next cell


End Sub

Open in new window


Saurabh...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey SmithOwnerAuthor Commented:
Still getting the same results, Saurabh:  The numbers in the Columns with Green Fill Color (and a "Y" in Row 7) still have a Green Font and are not visible.

Jeff
Saurabh Singh TeotiaCommented:
Not sure what you are doing i just ran the code and it does what you are looking for..

Enclosed is the file for your reference...

Saurabh...
EE-Example-1.xlsm
Jeffrey SmithOwnerAuthor Commented:
Ok, I'm not sure what is going on yet, either, Saurabh.  I can see it is working on your file (Thanks !!), and I can get it to work in one version of my file but for some reason, not another.

I've got a lot of things running on my PC and it's probably been a week since I've re-booted.  So, let me work on shutting down, re-booting and trying again.  Not sure what might be the difference in that one file of mine, but I'll do some code comparisons between them just to make sure this isn't going to come back and bite me in the future.  

I'll post back.

Jeff
Jeffrey SmithOwnerAuthor Commented:
Well, I can only conclude that I had too much running and/or it was too long since a re-boot, because once I got through that, your code is now working in all 3 files.  

I've got some serious bottlenecks in (for example) the procedure "View_Hotel_Schedule" that calls this procedure and while your code runs quick, this overall calling procedure has started taking a very long time to run.  I'm going to try and figure out why but it's possible you'll have an opportunity to see this file, again, Saurabh, if I end up posting another question on this issue. ;-)

Thanks for your help!

Jeff
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.