Cell Formatting

[boakley]

Hi everyone!

I have a column of cells that is for peoples names (column C).
I have three more columns on th3 table that are for entering numbers (columns F, H and J).

If that person is absent that day, I want to manually turn the cell with their name in it RED(cell C2) and then when I do that, I want it to automatically turn cells F2, H2 AND J2 RED and lock the cells from editing if possible. Any help?

 

[Special-K99]

How will you inidicate to Excel that they are absent that day?
Do you just mean you'll fill the cell in with red to indicate an absence?

Would be better maybe put an x in a column then get the cell name to turn red automatically along with the other cells.

 

[boakley]

How will you inidicate to Excel that they are absent that day?
Do you just mean you'll fill the cell in with red to indicate an absence?

Would be better maybe put an x in a column then get the cell name to turn red automatically along with the other cells.

So you mean create another column, then in the same row as the name, add an "X" into the new column for the respective row and have it turn everything red and lock it?

 

[Special-K99]

Typing an X in another column must be easier than selecting the Fill Color button.
Not sure about hwo to lock cells though.

 

[Joe4]

If that person is absent that day, I want to manually turn the cell with their name in it RED(cell C2) and then when I do that, I want it to automatically turn cells F2, H2 AND J2 RED

Once you determine how to tell if someone is absent, this part should be able to be accomplished quite easily with Conditional Formatting.

 

[Special-K99]

Select the cells you want to change
Assume column A is the absence indicator.

Conditional Formatting
New Rule
Use a formula to determine...

=($A1="X")
format as red

Placing an X in column A should turn the selected cells to red

 

[Special-K99]

In addition a formula cannot detect the colour of a cell (this is only possible with VBA), so Excel won't know you've filled the cell with red to indicate an absence, hence the requirement of a value ("X").

 

[boakley]

Select the cells you want to change
Assume column A is the absence indicator.

Conditional Formatting
New Rule
Use a formula to determine...

=($A1="X")
format as red

Placing an X in column A should turn the selected cells to red

That worked perfect! Great idea! Thank you!

 

[Dossfm0q]

Good Day All
Pls Alt+F11 click VBAProject worksheet where you work and copy Below Codes to there.
pls do not use Standard Module to be generate Auto

Private Sub Worksheet_Change(ByVal Target As Range)


Dim Rng As Range
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:="000"
On Error Resume Next
Cells.Locked = False
On Error GoTo 0
For Each Rng In Range("C1", Range("C" & Rows.Count).End(xlUp))
 R = Rng.Row


 If Cells(Rng.Row, 6) = "X" Or Cells(Rng.Row, 6) = "x" Then
    With Range("C" & R & ",F" & R & ":H" & R & ",J8")
     With .Font
            .Color = RGB(0, 0, 0)
     End With
    With .Interior
        .Color = RGB(255, 0, 0)
    End With
    .Locked = True
    End With
 Else
    With Range("C" & R & ",F" & R & ":H" & R & ",J8")
     With .Font
          .Color = RGB(0, 0, 0)
     End With
    With .Interior
        .Pattern = xlNone
    End With
    .Locked = True
    End With
 End If
Next
Application.ScreenUpdating = True


ActiveSheet.Protect Password:="000", DrawingObjects:=True, Contents:=True, Scenarios:=True




End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked = True And ActiveSheet.ProtectContents = True Then
UP = InputBox("Please enter Password")
On Error Resume Next
ActiveSheet.Unprotect Password:=UP
On Error GoTo 0
End If
End Sub

 

[Dossfm0q]

replace

With Range("C" & R & ",F" & R & ":H" & R & ",J8")
with
With Range("C" & R & ",F" & R & ":H" & R & ",J" & R & "")