This code needs to work on 2 worksheets

[Darren Smith]

I need to insert color into 2 sheets with the same command button.
How is this possible with my code?

Sub colorAbove(rng As Range)
    Dim i As Long, rrg As Range
    Dim ws As Worksheets
    
For Each ws In ThisWorkbook.Worksheets(Array("Job Card Master", "Job Card with Time Analysis"))

    For i = 1 To rng.Rows.Count
        Set rrg = rng.Rows(i)
  
        If WorksheetFunction.CountA(rrg) = 0 Then
            
            If rrg.Offset(1).Cells(3) <> "" Then
                
                rrg.Interior.ColorIndex = 36
            End If
        End If
    Next i
    Next ws
End Sub

 

[StephenCrump]

If the user clicks the command button, how do you want the code to get rng? Will this be the same range for both worksheets?

(It would be easier to conditionally format the entire range, rather than looping through each row. That way the colour will also be dynamic).

 

[Darren Smith]

The full code is below. They link together.

Private Sub Add_Lines_Color_And_Prices_Click()

TurnOff

    Call Delete_Color

    Dim ws As Worksheet, PartsList As Worksheet, JCM As Worksheet
    Dim Lastrow As Long, PartsListLastRow As Long, x As Long, JCMLastRow As Long
    Dim rngToCheck As Range, rng As Range, c As Range, DataRng As Range
    
    Set ws = ThisWorkbook.Worksheets("Job Card Master")
    Set rngToCheck = ws.Range("A13:Q299")
    Set c = ActiveSheet.Range("E13:E299")
    Set PartsList = ThisWorkbook.Worksheets("Parts List")
    
For Each ws In ThisWorkbook.Worksheets(Array("Job Card Master", "Job Card with Time Analysis"))

   For Each c In ws.Range("E13:E299")
    
    For Each rng In rngToCheck
     If rng.Interior.ColorIndex = 36 Then
     rng.Interior.Pattern = xlNone
     End If
    Next rng
    
    Lastrow = ws.Cells(Rows.Count, 3).End(xlUp).Row

    ws.Range("P13:P299").Value = Null
    
                If Left(c, 1) = "^" Then
                c.Font.ColorIndex = 54
                c.Font.Italic = True
                c.Font.Bold = True
                End If
                
                If Left(c, 1) = "*" Then
                c.Font.ColorIndex = 45
                c.Font.Italic = True
                c.Font.Bold = True
                End If
                
              ws.Range("P13:P299").Value = Null
    
                If Left(c, 1) = "^" Then
                c.Font.ColorIndex = 54
                c.Font.Italic = True
                c.Font.Bold = True
                End If
                
                If Left(c, 1) = "*" Then
                c.Font.ColorIndex = 45
                c.Font.Italic = True
                c.Font.Bold = True
                End If
    Next
    
     
        
                            colorAbove ws.Range("A13:Q61")
                            colorAbove ws.Range("A66:Q122")
                            colorAbove ws.Range("A127:Q183")
                            colorAbove ws.Range("A188:Q244")
                            colorAbove ws.Range("A249:Q" & Lastrow)
                           ws.Range("P13:P63").Formula = "= RoundUp(H13, 0) * O13"
                           ws.Range("P66:P122").Formula = "= RoundUp(H66, 0) * O66"
                           ws.Range("P127:P181").Formula = "= RoundUp(H127, 0) * O127"
                           ws.Range("P188:P244").Formula = "= RoundUp(H188, 0) * O188"
                           ws.Range("P249:P299").Formula = "= RoundUp(H249, 0) * O249"
                           Next ws
               

                   Call Add_PartNos
    TurnOn
    
End Sub

Sub colorAbove(rng As Range)
    Dim i As Long, rrg As Range
    Dim ws As Worksheets
    
For Each ws In ThisWorkbook.Worksheets(Array("Job Card Master", "Job Card with Time Analysis"))

    For i = 1 To rng.Rows.Count
        Set rrg = rng.Rows(i)
  
        If WorksheetFunction.CountA(rrg) = 0 Then
            
            If rrg.Offset(1).Cells(3) <> "" Then
                
                rrg.Interior.ColorIndex = 36
            End If
        End If
    Next i
    Next ws
End Sub

 

[Darren Smith]

If the user clicks the command button, how do you want the code to get rng? The code gets it`s range from the code above as see above

Will this be the same range for both worksheets? Yes

(It would be easier to conditionally format the entire range, rather than looping through each row. That way the colour will also be dynamic). Righto how could i do this in VBA

 

[Darren Smith]

Please could somebody help today with this

 

[StephenCrump]

Your Sub Private Sub Add_Lines_Color_And_Prices_Click() already loops through the two worksheets, but it calls colorAbove after you end the loop.

So why not simply:

Private Sub Add_Lines_Color_And_Prices_Click()

    Dim ws As Worksheet
    'etc

    For Each ws In Worksheets(Array("Job Card Master", "Job Card with Time Analysis"))
        'Do other stuff
        
        colorAbove ws.Range("A13:Q61")  'i.e. INSIDE the loop
        'etc
    
    Next ws

End Sub
Sub colorAbove(rng As Range)
    
    Dim i As Long, rrg As Range
    
    'Therefore no need to loop through worksheets here
    For i = 1 To rng.Rows.Count
        Set rrg = rng.Rows(i)
        If WorksheetFunction.CountA(rrg) = 0 Then
            If rrg.Offset(1).Cells(3) <> "" Then _
                rrg.Interior.ColorIndex = 36
        End If
    Next i
    
End Sub

 

[Darren Smith]

It needs to find every blank row on top of any filled row then fill color. I think that's the point of the counter?
Sorry not sure what you mean at the mo?

I`ve put my workbook below if that helps?

Transfer - Dropbox

Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!

www.dropbox.com

 

[StephenCrump]

It needs to find every blank row on top of any filled row then fill color. I think that's the point of the counter?
Sorry not sure what you mean at the mo?

If you run the code in Post #6, it will do precisely that for Range("A13:Q61") in both worksheets Job Card Master and Job Card with Time Analysis. As written in your code, I'm testing .Offset(1).Cells(3) for a non-blank, i.e. column C of the row below in this case.

Is this what you want to happen?

 

[Darren Smith]

Yes, that`s what I am trying to do.
I have done what you suggested but the Next WS error says "Invalid Next control variable reference"

 

[StephenCrump]

Can you post your code?