Formatting and Locking cells on a condition

[sanjaynaidubas]

hi,

i have a requirement where i have to format the row based on the input in Column A
also i have to lock few cells of that Row based on the input.
for Example say the Data entered in column 'A' is 'MAIN', then i need to change the color of that row and also lock Cells of C,D and E for that row only.
and say Column C is mandatory, then if user selects another row, without entering the data, alert can pop up asking to fill it.

i have read through forums but none is working

i was able to change the color of the row with is code. but the problem is
the color changes when i re focus the cell 'A' , (after i have Type Main, then i go back to the cell color changes)
where as i wanted, on blur of cell 'A' if data entered is 'MAIN', then change color and lock the cells.

this is what i could figure out from the net.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
RecType = UCase(Target.Text)
Select Case RecType
Case "MAIN"
Range("A" & Target.Row, "H" & Target.Row).Interior.ColorIndex = 10
Case "GR"
Range("A" & Target.Row, "H" & Target.Row).Interior.ColorIndex = 15
End Select
End Sub







Please help!.
thanks in Advance
NOTE : i have to use VBA (Excel versions will vary in this case)


Regards,
Sanjay Naidu

 

[lancer102rus]

How can user fill Column C if it is locked?

First 2 problems you can solve by this code:

Private Sub Worksheet_Change(ByVal Target As Range)    
    If Target.Column <> 1 Or Target.Count > 1 Then Exit Sub
    
    Unprotect "111"   ' Here can be your own password
    Select Case UCase(Target.Text)
    Case "MAIN"
        Cells(Target.Row, "A").Resize(1, 8).Interior.ColorIndex = 10
        Cells(Target.Row, "C").Resize(1, 3).Locked = True
    Case "GR"
        Cells(Target.Row, "A").Resize(1, 8).Interior.ColorIndex = 15
    Case Else
        Cells(Target.Row, "A").Resize(1, 8).Interior.ColorIndex = xlColorIndexNone
        Cells(Target.Row, "C").Resize(1, 3).Locked = False
    End Select
    
    Protect "111"     ' Here can be your own password
End Sub

But first you need to select all cells. Press Ctrl+1 and unmark "Locked" in the "Protection" tab

 

[sanjaynaidubas]

How can user fill Column C if it is locked?

First 2 problems you can solve by this code:

Private Sub Worksheet_Change(ByVal Target As Range)    
    If Target.Column <> 1 Or Target.Count > 1 Then Exit Sub
    
    Unprotect "111"   ' Here can be your own password
    Select Case UCase(Target.Text)
    Case "MAIN"
        Cells(Target.Row, "A").Resize(1, 8).Interior.ColorIndex = 10
        Cells(Target.Row, "C").Resize(1, 3).Locked = True
    Case "GR"
        Cells(Target.Row, "A").Resize(1, 8).Interior.ColorIndex = 15
    Case Else
        Cells(Target.Row, "A").Resize(1, 8).Interior.ColorIndex = xlColorIndexNone
        Cells(Target.Row, "C").Resize(1, 3).Locked = False
    End Select
    
    Protect "111"     ' Here can be your own password
End Sub

But first you need to select all cells. Press Ctrl+1 and unmark "Locked" in the "Protection" tab

hi lancer102rus,

Thank you so much for replying.
this code does solve the first two problems
atcually "Press Ctrl+1 and unmark "Locked" in the "Protection" tab" did the trick, else my whole Sheet was getting locked.
and formatting works fine too
only mandatory part i need to figure out.

and my requirement is that if Column A is MAIN then i should not fill anything in Column C, so i wanted it locked

thanks again u rock

 

[lancer102rus]

Hi, sanjaynaidubas!
When cell in column A = "MAIN" then Column C must be empty. But when cell in column A <> "MAIN" then Column C must be filled. Right?
If so, then i`d recommend to alert before closing workbook.

 

[sanjaynaidubas]

Hi, sanjaynaidubas!
When cell in column A = "MAIN" then Column C must be empty. But when cell in column A <> "MAIN" then Column C must be filled. Right?
If so, then i`d recommend to alert before closing workbook.

hi lancer102rus,
yes will try that thanks.