help with vba adaption

[blueroost]

Came across this code but trying to adapt it slightly

The code searches column e for the word bottle, copies and pastes the whole row where the word bottle appears in sheet 2.

What i want to change is ;

search a range ie - a4:f20 (as opposed to only column e)

Search criteria will be by font colour (blue for example ) Font.ColorIndex = 5 and only if there is no other fill colour in the cell.

Have tried to adapt but i keep getting errors , Original Code;

Sub SearchForString()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 4
LSearchRow = 4

'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column E = "Mail Box", copy entire row to Sheet2
If Range("E" & CStr(LSearchRow)).Value = "Mail Box" Then

'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Sheet2 in next row
Sheets("Sheet2").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("Sheet1").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub


Many Thanks

 

[blueroost]

bump

 

[blueroost]

Hi Guys, had a lot of views but not a lot of replies

can you let me know if what i am looking for is impossible, i might give up on the whole idea as i am spending a lot of thankless hours teying to find some sort of solution

thanks

 

[Norie]

Why don't you search/filter based on the date rather than formatting?

Working with formatting would be hard enough, but now you've mentioned that the formatting is via conditional formatting which is a different thing entirely.

It is possible to check conditional formatting but it's actually easier to do that using the condition not the format.

So in this case you probably want to actually be looking at the date value in the cell.

 

[blueroost]

Yep, date range easier, too busy concentrating on the font colour when the date range does the same thing.

many thanks

 

[Norie]

No problem, and thanks for posting the link to the thread with the sample data.

I'll have a look at it later if I can ever sort out my new 'plug and play' USB wireless adapter.