cell formatting

[Duncan]

Hi guys,

I am having troubles with some code that I am adapting, I want it to
make the entire row red on the found cell but it keeps bugging out, I
am not sure what I have done wrong here! any ideas?...(ill paste the
full sub below)

Private Sub LiveSubmit_Click()
Dim si As New SystemInfo
Dim retval As Variant
Dim wks As Worksheet
Dim FoundCell As Range
Dim myCell As Range
Dim ActSheet As Worksheet
Dim resp As Long

If Len(LiveBcNo.Value) > 13 Then
LiveBcNo.Value = Mid(LiveBcNo.Value, 2, 13)
End If

If LiveBcNo.Value <= "" Then
If LiveYear <= "" Then
MsgBox "Must Fill in Levy Year", vbOKOnly
Exit Sub
Else
If LiveRegNo <= "" Then
MsgBox "Must Fill in Registration number", vbOKOnly
Exit Sub
Else
LiveBcNo.Value = LiveYear & "000" & LiveRegNo & "11"
End If
End If
End If

Set ActSheet = ActiveSheet

If IsNumeric(LiveBcNo.Value) = False Then Exit Sub
If Trim(LiveBcNo.Value) = "" Then Exit Sub

resp = MsgBox(prompt:="Are you sure you want to clean up: " _
& LiveBcNo.Value & "?", Buttons:=vbYesNo)

If resp = vbNo Then
Exit Sub
End If

For Each wks In ActSheet.Parent.Worksheets
If wks.Name <> ActSheet.Name Then
'do nothing
Else
Do
With wks.Range("a:a")
Set FoundCell = .Find(What:=LiveBcNo.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, LookAt:=xlWhole,
_
SearchOrder:=xlByRows, _
SearchDirection:=xlNext)
End With

If FoundCell Is Nothing Then
'done with that worksheet
Exit Do
Else

FoundCell.EntireRow.Select

With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With


End If
Loop
End If
Next wks

LiveBcNo.Value = ""
LiveRegNo.Value = ""
LiveBcNo.SetFocus
End Sub

[Martin]

Your code looks OK. Just as an alternative, how about combining the line
that selects the foundcell's row with the formatting With/End With that comes
after it into:
FoundCell.EntireRow.Interior.ColorIndex = 3



"Duncan" wrote:

> Hi guys,
>
> I am having troubles with some code that I am adapting, I want it to
> make the entire row red on the found cell but it keeps bugging out, I
> am not sure what I have done wrong here! any ideas?...(ill paste the
> full sub below)
>
> Private Sub LiveSubmit_Click()
> Dim si As New SystemInfo
> Dim retval As Variant
> Dim wks As Worksheet
> Dim FoundCell As Range
> Dim myCell As Range
> Dim ActSheet As Worksheet
> Dim resp As Long
>
> If Len(LiveBcNo.Value) > 13 Then
> LiveBcNo.Value = Mid(LiveBcNo.Value, 2, 13)
> End If
>
> If LiveBcNo.Value <= "" Then
> If LiveYear <= "" Then
> MsgBox "Must Fill in Levy Year", vbOKOnly
> Exit Sub
> Else
> If LiveRegNo <= "" Then
> MsgBox "Must Fill in Registration number", vbOKOnly
> Exit Sub
> Else
> LiveBcNo.Value = LiveYear & "000" & LiveRegNo & "11"
> End If
> End If
> End If
>
> Set ActSheet = ActiveSheet
>
> If IsNumeric(LiveBcNo.Value) = False Then Exit Sub
> If Trim(LiveBcNo.Value) = "" Then Exit Sub
>
> resp = MsgBox(prompt:="Are you sure you want to clean up: " _
> & LiveBcNo.Value & "?", Buttons:=vbYesNo)
>
> If resp = vbNo Then
> Exit Sub
> End If
>
> For Each wks In ActSheet.Parent.Worksheets
> If wks.Name <> ActSheet.Name Then
> 'do nothing
> Else
> Do
> With wks.Range("a:a")
> Set FoundCell = .Find(What:=LiveBcNo.Value, _
> after:=.Cells(.Cells.Count), _
> LookIn:=xlValues, LookAt:=xlWhole,
> _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext)
> End With
>
> If FoundCell Is Nothing Then
> 'done with that worksheet
> Exit Do
> Else
>
> FoundCell.EntireRow.Select
>
> With Selection.Interior
> .ColorIndex = 3
> .Pattern = xlSolid
> End With
>
>
> End If
> Loop
> End If
> Next wks
>
> LiveBcNo.Value = ""
> LiveRegNo.Value = ""
> LiveBcNo.SetFocus
> End Sub
>
>

[Duncan]

Martin,

that saves a few lines, thanks! I think i copied that 'with' straight
out of the help, im still learning vba and it is quite a steep learning
curve but i think im getting there.....

I worked out why it bugged out, the loop that was in from my other sub
was looping itself into infinity, so i changed the code to add "SCA" to
the end of the number and then when it looped back it didnt find a
match again.

Duncan