+ Reply to Thread
Results 1 to 2 of 2

Two Codes Conflicting

Hybrid View

waqarqrl Two Codes Conflicting 12-16-2013, 05:48 AM
waqarqrl Re: Two Codes Conflicting 12-16-2013, 05:54 AM
  1. #1
    Forum Contributor
    Join Date
    07-25-2012
    Location
    Pakistan
    MS-Off Ver
    Office 365 for MAC
    Posts
    191

    Two Codes Conflicting

    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
    Last edited by waqarqrl; 12-16-2013 at 05:55 AM.

  2. #2
    Forum Contributor
    Join Date
    07-25-2012
    Location
    Pakistan
    MS-Off Ver
    Office 365 for MAC
    Posts
    191

    Re: Two Codes Conflicting

    i did but with wrong parameter

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conflicting formatting results
    By jacob@thepenpoint in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-25-2012, 04:52 PM
  2. 2 Macros conflicting with each other
    By brianf in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-16-2010, 04:14 AM
  3. conflicting codes for change event
    By dado6612 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-08-2010, 01:26 AM
  4. Conflicting tables in the same row
    By DarXide in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 05-16-2009, 12:30 PM
  5. Conflicting VBA codes
    By SSA_111 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-21-2006, 05:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1