Problem using "range.find" in VBA

[pacfleck]

Does anybody knows what is the problem with this code:

k = RGB(255,0,0)
For a = 5 To 7
For Each c In Worksheets(a).Range(Worksheets(a).Cells(5, 4), Worksheets(a).Cells(5 + Worksheets(2).Cells(1, 1), 24))
If c.Font.Color = k Then
If Not (Range("D:D").Find(c.Value) Is Nothing) Then
If Worksheets(4).Cells(Worksheets(4).Range("d:d").Find(c.Value).Row, 8) = _
Worksheets(a).Cells(c.Row, 3) Then
c.Font.Color = RGB(0, 0, 0)
Else
Cells(Range("d:d").Find(c).Row, 8).Font.Color = RGB(255, 0, 0)
End If
End If
End If
Next
Next
I'm having problem with this part: If Not (Range("D:D").Find(c.Value) Is Nothing) Then
It never found what should be found. By the way, it is a date what it should look for. The "c.value" it is a date.

I have 7 sheets. In the sheet 4, there is a column, in the code you can see range("D:D"). Here I have some dates. These dates are in the sheets 5, 6 and 7 too. In the sheets 5, 6 and 7 there are a lot of dates, much more than exists in the sheet 4. The code should look for dates that are with the color red ("RGB(255,0,0)") in the sheets 5, 6 and 7. Than, when it found a date with this color, it have to look in the sheet 4 and confirm that this date really exists there. Now it should change the color to black in the date that is in the sheets 5, 6 or 7. The problem that I'm having is that it found the cells that the color of the font is red in the sheets 5, 6 and 7, but when it have to look for this date in the sheet 4, it doesn't found anything.

[mdmackillop]

Can you explain what the code should do?

[pacfleck]

Explained a little bit better. I think you can understand now. Thanks.

[mdmackillop]

Can you post a workbook with sample data? Manage Attachments in Go Advanced reply section.

[pacfleck]

Thanks, I'm new in this forum. Attached.

[Zack Barresse]

How about referencing the correct worksheet? You would need to do this just prior to the Range("D:D"), otherwise it's just referencing whatever the active sheet is.

HTH

[pacfleck]

I have already tried it. Didn't work. The module is executed only in the worksheet(4).

[mdmackillop]

Option Explicit
Public Sub Confirma()
Dim Rng As Range
Dim FirstAddress As String
Dim k, cel As Range, c As Range
Dim a As Long
Set Rng = Sheets(4).Range("D2:D36")
k = RGB(255, 0, 0)
For Each cel In Rng
For a = 5 To 7
FirstAddress = ""
With Sheets(a).UsedRange
Set c = .Find(cel)
If Not c Is Nothing Then
Do
If c.Font.Color = k Then
FirstAddress = c.Address
c.Font.Color = RGB(0, 0, 0)
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
Next
Next
End Sub

[Zack Barresse]

Well that means your code which you've posted is not current. Can you post the full current code?

[pacfleck]

I post an attachment. The code is executed when I press a button that is in the sheet 4.

[pacfleck]

mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87), do you know why my code isn't working?