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
)
Off topic a bit, but I guess that could explain some other things we noticed before…
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
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
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
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
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
And yet here we are in precisely that situation hence my point. :)YasserKhalil wrote: ↑07 Apr 2021, 11:01Thanks 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.