Hi VBA Fox
Please guide me as i have to use these both codes.
Both code are conflicting . May be some variable problem.
Regards
First Code
It select the Row and Colomn and red today entry
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor As Integer
Dim fmcCurr As FormatCondition
Dim lngLastRow As Long
Dim lngLastCol As Long
Application.ScreenUpdating = 0
Application.EnableEvents = 0
'// Amended routine found on this Web site
'// Note: Don't use IF you have Conditional
'// formating that you want to keep!
'// On error resume in case
'// user selects a range of cells
On Error Resume Next
iColor = Target.Interior.ColorIndex
'Leave On Error ON for Row offset errors
If iColor < 0 Then
iColor = 6
Else
iColor = iColor + 1
End If
'// Need this test incase Font color is the same
If iColor = Target.Font.ColorIndex Then iColor = iColor + 1
Me.Unprotect
Cells.FormatConditions.Delete
With Union(Cells(Target.Row, 1).Resize(1, Target.Column), _
Cells(1, Target.Column).Resize(Target.Row - 1, 1)) 'Rows(Target.Row)
.FormatConditions.Add Type:=2, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = iColor
.FormatConditions(1).Font.Bold = True
End With
On Error Resume Next
lngLastRow = Columns(1).Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
LookAt:=xlPart, LookIn:=xlValues).Row
If Err <> 0 Then
lngLastRow = 0
End If
lngLastCol = Rows(2).Find(What:="*", After:=[A2], _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
LookAt:=xlPart, LookIn:=xlValues).Column
If Err <> 0 Then
lngLastCol = 0
End If
If lngLastRow > 0 _
And lngLastCol > 0 Then
Set fmcCurr = Range("K3").Resize(lngLastRow - 2, lngLastCol - 10) _
.FormatConditions.Add(Type:=2, Formula1:="=$H3<SUM($K3:K3)")
With fmcCurr
.Interior.ColorIndex = 3
.Font.Bold = False
End With
Set fmcCurr = Range("B3").Resize(lngLastRow - 2, 2) _
.FormatConditions.Add(Type:=2, Formula1:="=$H3<SUM($K3:" & Cells(3, lngLastCol).Address(False, True) & ")")
With fmcCurr
.Interior.ColorIndex = 3
.Font.Bold = False
End With
Set fmcCurr = Nothing
End If
With Me
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeConstants).Locked = True
.Protect
End With
Application.ScreenUpdating = 1
Application.EnableEvents = 1
ActiveSheet.Unprotect
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Font.ColorIndex = 3
End Sub
Second Code
it protect after every entry
Private Sub Worksheet_Change(ByVal Target As Range)
With Me
.Unprotect
.Cells.Locked = False
.Cells.SpecialCells(xlCellTypeConstants).Locked = True
.Protect
End With
End Sub
Bookmarks