Remove CF's, DV's, Formulas & Protect

[dparteka]

I've been attempting to put this together and have concluded it's just beyond my skill level, any help would be awesome and greatly appreciated... thanks for looking.

I'm using the code shown below which looks in column-A for "+", then for all rows that have this character it locks each cell in that row and then protects the sheet, I'm hoping to expand this to also include the following only in those rows that have "+" in column-A:
a. Remove all conditional formats
b. Remove all data validations
c. Replace all formulas with Values
d. Change all fonts to black
e. Change all “+” to “-“

Here's everything that should happen
1. Unprotect the worksheet
2. Remove all conditional formats
3. Remove all data validations
4. Replace all formulas with Values
5. Change all fonts to black
6. Change all “+” to “-“
7. Lock each cell
8. Protect the worksheet

Private Sub CommandButton2_Click()
  ActiveSheet.Unprotect
  Dim rChk As Range, r1st As Range
  Set r1st = Columns("A").Find(What:="+", _
  after:=Cells(Rows.Count, "A"), _
  LookIn:=xlValues, LookAt:=xlPart, _
  searchdirection:=xlNext)
  If Not r1st Is Nothing Then '+ is found
  Set rChk = r1st
  Do
  rChk.EntireRow.Locked = True
  Set rChk = Columns("A").FindNext(after:=rChk)
  Loop While rChk.Address <> r1st.Address ' else endless loop
  End If
  Set r1st = Nothing
  Set rChk = Nothing
  ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
  , AllowSorting:=True, AllowFiltering:=True
End Sub

 

[chirayu]

Try This. Didn't really understand your replace + with - request cos formula to value = you get the output. not the formula. right now my macro checks the formula rather than output so edit accordingly

Sub CheckyWecky()

ActiveSheet.Unprotect Password:="1"

Dim Rng As Range
Dim SubRng As Range

Set Rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each SubRng In Rng
  
    If SubRng.Formula Like "*+*" Then
        SubRng.FormatConditions.Delete
        SubRng.Validation.Delete
        SubRng = SubRng.Value
        SubRng.Font.Color = RGB(0, 0, 0)
        SubRng.Replace What:="+", Replacement:="-", LookAt:=xlPart
        SubRng.EntireRow.Locked = True
    Else
        SubRng.EntireRow.Locked = False
    End If
Next SubRng

ActiveSheet.Protect Password:="1"


End Sub

 

[dparteka]

Thank you for helping... it's not working and I'm not sure what's happening but it may have to do with the formula in column-A. When I run the macro it wipes out all the formulas in column-A, I'm guessing because there's a "+" in the actual formula. The reason for the replacement of + to - was so the next time the macro runs it doesn't look at the rows that have the -. When running the macro it pretty much just does the + - replacement and nothing else.

 

[chirayu]

Ah ok my macro was reading the + in the formula rather than the + as a value. I'm assuming then that the formula itself doesn't need to be edited but instead the cell needs to be value pasted & then changed to a - etc
I was confused by your requirements because I thought you meant change the + inside the formula to a - and then value paste

Sub CheckyWecky()

ActiveSheet.Unprotect Password:="1"

Dim Rng As Range
Dim SubRng As Range

Set Rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each SubRng In Rng
 
        If SubRng = "+" Then
            SubRng.FormatConditions.Delete
            SubRng.Validation.Delete
            SubRng = "-"
            SubRng.Font.Color = RGB(0, 0, 0)
            SubRng.EntireRow.Locked = True
        Else
            SubRng.EntireRow.Locked = False
        End If
Next SubRng

ActiveSheet.Protect Password:="1"

End Sub

 

[dparteka]

chirayu... it's like magic, very impressive. In the first code you provided you included SubRng = SubRng.Value to replace formulas with values. I tried adding this to your latest code but it's not working. I may not have been clear on this, cells in columns A, E, L, N, O, P, Q have formulas that need to be replaced with values, how would I go about doing that?

 

[chirayu]

SubRng only refers to the cells listed in Rng i.e. in the code this bit tells the macro to count number of non empty cells in column A and store it as a Range

Set Rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)

Then this bit starts a loop to run through each cell address that was stored in Rng. Each individual cell is essentially a SubRng

For Each SubRng In Rng
'Rest of the macro
Next SubRng

So When we say SubRng = SubRng.Value it means take the value of the individual cell & put it in that cell - essentially replacing the formula.

If you want to replace other cells in the same row then you need to store the row number from the SubRng as per modified code below.

Sub CheckyWecky()

ActiveSheet.Unprotect Password:="1"

Dim Rng As Range
Dim SubRng As Range
Dim SubRow As Integer

Set Rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each SubRng In Rng

    If SubRng = "+" Then
        SubRow = SubRng.Row
        SubRng.FormatConditions.Delete
        SubRng.Validation.Delete
        SubRng = "-"
        SubRng.Font.Color = RGB(0, 0, 0)
        SubRng.EntireRow.Locked = True
        Range("E" & SubRow) = Range("E" & SubRow).Value
        Range("L" & SubRow) = Range("L" & SubRow).Value
        Range("N" & SubRow) = Range("N" & SubRow).Value
        Range("O" & SubRow) = Range("O" & SubRow).Value
        Range("P" & SubRow) = Range("P" & SubRow).Value
        Range("Q" & SubRow) = Range("Q" & SubRow).Value
    Else
        SubRng.EntireRow.Locked = False
    End If
  
Next SubRng

ActiveSheet.Protect Password:="1"

End Sub

Right now the rest of the stuff like remove data validation/ conditional formats/ font colour black etc will only work for SubRng but if you want those to work for the entire row then change Below bits as shown

'Replace This
SubRng.FormatConditions.Delete
SubRng.Validation.Delete
SubRng.Font.Color = RGB(0, 0, 0)

'With This
SubRng.EntireRow.FormatConditions.Delete
SubRng.EntireRow.Validation.Delete
SubRng.EntireRow.Font.Color = RGB(0, 0, 0)

 

[dparteka]

Perfect, does everything I was looking for, thanks for the education, you're the best