marco to find empty cell in range

[sl1990]

hi i need a macro to find empty cells in any row that has a color fill in
range B:R? I also need to run this over 150 sheets (named sheet1 to sheet150)

any idea how i would do this ?

thanks in andvance

 

[sl1990]

bump?

 

[Weaver]

First off, you're going to have to devise a way of looping through all the sheets in the workbook.

Sub sl1990()
    For Each ws In ActiveWorkbook.Worksheets
        Debug.Print ws.Name 'example code only
    Next ws
End Sub

Then we need a way for looking for our colour. Do we need to check all columns, or just the first one in each row?

We also need to know if the colour is the result of conditional formatting.

What do you want to do with the cells, once you've found 'em?

 

[mouse88]

Can you explain what you want a bit better and ill see if I can help.

From what I can imagine you want to loop through the range B:R, check if the cells have been filled with a color and check if they are blank.

Is this correct, and what do you want to do if it matches your criteria?

 

[mouse88]

This should work. Just change the part where im chaging the cells value to "Test" to do whatever you want.

Public Sub TestSub()
'Declare Variables'
Dim wkSheet As Worksheet
Dim myRange As Range
Dim cell As Range
'Loop Worksheets'
For Each wkSheet In ActiveWorkbook.Worksheets
    'Loop Range'
    For Each cell In wkSheet.Range("B:R")
        If cell.Interior.ColorIndex <> xlNone And cell.Value = vbNullString Then
            'You Have A Match'
            cell.Value = "Match"
        End If
    Next cell
Next wkSheet
End Sub

Just to let you know, looping through the two whole columns can take a while. If you know your worksheets aren't going to pass a certain row number then I would specify a maximum row number to speed it up.

 

[Weaver]

You could try:

For Each cell In Intersect(wkSheet.UsedRange, wkSheet.Range("B:R"))

rather than

For Each cell In wkSheet.Range("B:R")

Also, it'll go faster if you drop in

 application.screenupdating = false

at the beginning of the code (or any time before the looping starts)