fill in cells according to selected optionbutton

**paul_pearson** · 09-11-2013, 11:06 AM

Hi

I have the cells fill in with color for Production,Planned Stop and Internal for Machine Stoppage......

1. How can the code be changed so that when Machine Stoppage is used it fills in the cells (eg XFLOW A Sheet) with color depending on which is selected between Internal or External...i can get it to fill in for Internal but not External

2. When Daily Total is selected (commandbutton) it also colors the rows where all Sum Totals are ... can the rows which have any Totals and Grand Totals entered have NO color in these rows

Thanks

PP

**paul_pearson** · 09-12-2013, 03:07 PM

**bakerman** · 09-13-2013, 06:17 AM

Code:

            With .Range("B" & Rows.Count).End(xlUp)
                .Offset(, 1).Resize(, 16).Interior.ColorIndex = 34
                .Offset(, IIf(OptionButton4, 8, 13)).Resize(, 5).Interior.ColorIndex = xlNone
            End With

Code:

Sub SubTotalize()
   
    With Sheets("XFLOW A")
       .Unprotect Password:="abc"
       .Range("B7:Q" & .Cells(Rows.Count, 2).End(xlUp).Row).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4, 7, 12, 16), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        With .Range("B" & Rows.Count).End(xlUp)
            .Offset(-1, 1).Resize(2, 16).Interior.ColorIndex = xlNone
        End With
       .Protect UserInterfaceOnly:=True, Password:="abc"
    End With
    
End Sub

**paul_pearson** · 09-13-2013, 07:37 AM

Thanks Bakerman

I am confident this is the last part and the workbook is completed

If i run this code below with no entries in the data sheet it brings up an error...can something be added in the code so that if no data is on the sheet it brings up a message "no data to sum" .. this will stop the error

Code:


Sub SubTotalize()
   
    With Sheets("XFLOW A")
       .Unprotect Password:="abc"
       .Range("B7:Q" & .Cells(Rows.Count, 2).End(xlUp).Row).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3, 4, 7, 12, 16), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        With .Range("B" & Rows.Count).End(xlUp)
            .Offset(-1, 1).Resize(2, 16).Interior.ColorIndex = xlNone
        End With
       .Protect UserInterfaceOnly:=True, Password:="abc"
    End With
    
End Sub

**paul_pearson** · 09-13-2013, 07:52 AM

Another

On the data sheet XFLOW A for 13/9/2013 & 14/9/2013 for Total the rows are blue....can all rows with either Total or Grand Total have no color

Thanks again

**bakerman** · 09-13-2013, 09:48 AM

Put this on top off SubTotalize.

Code:

If Sheets("XFLOW A").Range("B6").CurrentRegion.Rows.Count = 2 Then Exit Sub

**paul_pearson** · 09-13-2013, 10:26 AM

Thanks bakerman

Can this one be fixed...if so this is finished

On the data sheet XFLOW A for 13/9/2013 & 14/9/2013 for Total the rows are blue....can all rows with either Total or Grand Total have no color

Thanks again