Findformat interior color doesn't work on excel 2010

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Findformat interior color doesn't work on excel 2010

Post by Doc.AElstein »

(
HansV wrote:
07 Apr 2021, 15:10
Excel 2016, 2019 and 365 .... share the same core, so they will most probably handle colors the same way. ...
Off topic a bit, but I guess that could explain some other things we noticed before…
https://eileenslounge.com/viewtopic.php ... 81#p276881
http://www.eileenslounge.com/viewtopic. ... 30#p262102
)
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

.FindFormat with .Find is possibly a bit quirky...

Post by Doc.AElstein »

I am getting some inconsistent result with coding using the .FindFormat in conjunction with the .Find

Example: Consider these sets of results.

Typical results for Excel 2010.
This version of my slow looping macro works, and the Debug.Print code line of
UsdRng.Item(Cnt).Interior.Color & " " & UsdRng.Item(Cnt).Address
gives me
15261367 $E$17

Code: Select all

Sub Colored_Cell2c() '  https://eileenslounge.com/viewtopic.php?f=30&t=36380
    Dim LastTableCell, LastColoredCell, Ws As Worksheet, lr8 As Long
    Set Ws = ThisWorkbook.Sheets("DATA")
    lr8 = Ws.ListObjects("Table1").Range.Columns(8).Cells.Find("*", SearchDirection:=xlPrevious).Row
Dim Cnt As Long, UsdRng As Range: Set UsdRng = Ws.UsedRange
    For Cnt = Ws.UsedRange.Cells.Count To 1 Step -1   '                                  Excel 2003                                 Excel 2007
        If UsdRng.Item(Cnt).Interior.Color = 15261367 Or UsdRng.Item(Cnt).Interior.Color = 16764057 Or UsdRng.Item(Cnt).Interior.Color = 15261110 Then
         Let LastColoredCell = UsdRng.Item(Cnt).Address
         Debug.Print UsdRng.Item(Cnt).Interior.Color & "   " & UsdRng.Item(Cnt).Address
         Exit For
        Else
         ' Do Nothing
        End If
    Next Cnt
 Let LastTableCell = Ws.Range("i" & lr8).Address
 Let Ws.PageSetup.PrintArea = Ws.Range(LastColoredCell, LastTableCell).Address
 Application.FindFormat.Clear
End Sub

This macro version of Yasser’s macro usually works for me in Excel 2010

Code: Select all

Sub Colored_Cell_15261367()  ' For Excel 2010
Dim LastTableCell, LastColoredCell, Ws As Worksheet, lr8 As Long
 Set Ws = ThisWorkbook.Sheets("DATA")
 Let lr8 = Ws.ListObjects("Table1").Range.Columns(8).Cells.Find("*", SearchDirection:=xlPrevious).Row
 Application.FindFormat.Clear
 Application.FindFormat.Interior.Color = 15261367
    If Ws.UsedRange.Find("", , , , , 2, , , True) Is Nothing Then Debug.Print " .Find("", , , , , 2, , , True)  is Nothing for  .FindFormat.Interior.Color = 15261367 "
 LastColoredCell = Ws.UsedRange.Find("", , , , , 2, , , True).Offset(, -3).Address
 LastTableCell = Ws.Range("i" & lr8).Address
 Ws.PageSetup.PrintArea = Ws.Range(LastColoredCell, LastTableCell).Address
 Application.FindFormat.Clear
End Sub

That makes sense

_.____________________________

Typical results for Excel 2007
That same previous version of my slow looping macro, Sub Colored_Cell2c() , works, and the Debug.Print code line of
UsdRng.Item(Cnt).Interior.Color & " " & UsdRng.Item(Cnt).Address
gives me
15261110 $E$17


But this macro version of Yasser’s macro craps out because
.Find(", , , , , 2, , , True) is Nothing
for
.FindFormat.Interior.Color = 15261110

Code: Select all

Sub Colored_Cell_15261110()  ' For Excel 2007
Dim LastTableCell, LastColoredCell, Ws As Worksheet, lr8 As Long
 Set Ws = ThisWorkbook.Sheets("DATA")
 Let lr8 = Ws.ListObjects("Table1").Range.Columns(8).Cells.Find("*", SearchDirection:=xlPrevious).Row
 Application.FindFormat.Clear
 Application.FindFormat.Interior.Color = 15261110
    If Ws.UsedRange.Find("", , , , , 2, , , True) Is Nothing Then Debug.Print " .Find("", , , , , 2, , , True)  is Nothing for  .FindFormat.Interior.Color = 15261110 "
 LastColoredCell = Ws.UsedRange.Find("", , , , , 2, , , True).Offset(, -3).Address
 LastTableCell = Ws.Range("i" & lr8).Address
 Ws.PageSetup.PrintArea = Ws.Range(LastColoredCell, LastTableCell).Address
 Application.FindFormat.Clear
End Sub

That doesn’t make sense

_.___________________________________


Typical results for Excel 2003
The same version of my slow looping macro works , Sub Colored_Cell2c() , and the Debug.Print code line of
UsdRng.Item(Cnt).Interior.Color & " " & UsdRng.Item(Cnt).Address
gives me
16764057 $E$17

This macro version of Yasser’s macro usually works in Excel 2003

Code: Select all

Sub Colored_Cell_16764057()  ' For Excel 2003
Dim LastTableCell, LastColoredCell, Ws As Worksheet, lr8 As Long
 Set Ws = ThisWorkbook.Sheets("DATA")
 Let lr8 = Ws.ListObjects("Table1").Range.Columns(8).Cells.Find("*", SearchDirection:=xlPrevious).Row
 Application.FindFormat.Clear
 Application.FindFormat.Interior.Color = 16764057
    If Ws.UsedRange.Find("", , , , , 2, , , True) Is Nothing Then Debug.Print " .Find("", , , , , 2, , , True)  is Nothing for  .FindFormat.Interior.Color = 16764057 "
 LastColoredCell = Ws.UsedRange.Find("", , , , , 2, , , True).Offset(, -3).Address
 LastTableCell = Ws.Range("i" & lr8).Address
 Ws.PageSetup.PrintArea = Ws.Range(LastColoredCell, LastTableCell).Address
 Application.FindFormat.Clear
End Sub

That makes sense


_._________


So I am not quite sure what is going on. I have no experience with .FindFormat but I have found myself and also have read of bugs and problems with .FindNext
So maybe things of the nature .Find_____ are a bit quirky and should be avoided.

I have had a lot of success with .Find
Maybe it’s a case of when a function works well, then likely the next version or advancement of it won’t… - that seems to be a general conclusion , as the saying goes, If it ain’t broke then the next version or update will be..

My general feeling would be to avoid using .FindFormat with .Find, but I cannot think , yet, or a quicker way to do it
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Findformat interior color doesn't work on excel 2010 ? - does on mine...

Post by Doc.AElstein »

FWIW , this macro, which is a version similar to that of Yasser's original , is working for me in most of my Excel 2003 and Excel 2010
But for some strange reason it does not work in any of my Excel 2007
Those results tie up with my findings from my last post

Code: Select all

Sub Colored_Cell_3() '  https://eileenslounge.com/viewtopic.php?f=30&t=36380  http://www.eileenslounge.com/viewtopic.php?f=30&t=36380&p=282306#p282306
Dim LastTableCell, LastColoredCell, Ws As Worksheet, lr8 As Long
 Set Ws = ThisWorkbook.Sheets("DATA")
 Let lr8 = Ws.ListObjects("Table1").Range.Columns(8).Cells.Find("*", SearchDirection:=xlPrevious).Row
Dim Cnt As Long, UsdRng As Range: Set UsdRng = Ws.UsedRange
 Application.FindFormat.Clear
 Application.FindFormat.Interior.Color = 15261367
Dim RngTemp As Range: Set RngTemp = Ws.UsedRange.Find("", , , , , 2, , , True) ' .Offset(, -3)
    If RngTemp Is Nothing Then
    Application.FindFormat.Clear
    Application.FindFormat.Interior.Color = 15261110  '  Excel 2007
     Set RngTemp = Ws.UsedRange.Find("", , , , , 2, , , True) ' .Offset(, -3)
        If RngTemp Is Nothing Then
        Application.FindFormat.Clear
        Application.FindFormat.Interior.Color = 16764057  '  Excel  2003
        Set RngTemp = Ws.UsedRange.Find("", , , , , 2, , , True) ' .Offset(, -3)
            If RngTemp Is Nothing Then
            Exit Sub
            Else
             Let LastColoredCell = RngTemp.Offset(, -3).Address
             End If
        Else
         Let LastColoredCell = RngTemp.Address
        End If
    Else
     Let LastColoredCell = RngTemp.Offset(, -3).Address
    End If
    LastColoredCell = Ws.UsedRange.Find("", , , , , 2, , , True).Offset(, -3).Address
 Let LastTableCell = Ws.Range("i" & lr8).Address
 Let Ws.PageSetup.PrintArea = Ws.Range(LastColoredCell, LastTableCell).Address
 Application.FindFormat.Clear
End Sub
You do not have the required permissions to view the files attached to this post.
Last edited by Doc.AElstein on 07 Apr 2021, 18:59, edited 1 time in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Findformat interior color doesn't work on excel 2010

Post by YasserKhalil »

Thank you very much Mr. Alan for your great efforts in this topic.

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Findformat interior color doesn't work on excel 2010

Post by rory »

YasserKhalil wrote:
07 Apr 2021, 11:01
Thanks a lot for replies.
As for the point of "Your code should never assume that a Find will work", yes as there must be be at least a cell with that color in column E.
And the code should detect the last cell with that color. The original code is already working in office 365 but not on office 2010.
And yet here we are in precisely that situation hence my point. :)
Regards,
Rory