Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Problem deleting borders and formatting for new items copied



I have  a problem  when  try  copy  new  items from sheet1 to  sheet2  after  matching  based on column B  when  run  the  macro ,   highlight the new  items  for  sheet2 ,  then will  delete  borders from column D  and somtimes add borders  for  the  whole  row after last  column , also delete formatting as  in  original in sheet1.

how  I  can  fix this  problem ?



Selected Answer

Hi Leap

I think there's some problem with the file itself- Excel wouldn't let me save a modified version, saying something like  "...,unable to fix errors...".

I copied the sheets to a new file (attached) and added a With/ End With portion to the first part of your Module 1 code (changes in bold):

Sub UpdateSheet21()

Dim LR1 As Long, LR2 As Long, n As Long

LR1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
LR2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row

' First find (and add) any Brands which do not exist on Sheet 2
For Each Cll In Sheets("Sheet1").Range("B2:B" & LR1)
    If Not IsNumeric(Application.Match(Cll.Value, Sheets("Sheet2").Columns(2), 0)) Then 'if no Exact Match...
        LR2 = LR2 + 1
        Cll.Resize(1, 3).Copy Sheets("Sheet2").Range("B" & LR2)
        With Sheets("Sheet2")
            .Range("C" & LR2).Value = "" 'set to blank
            .Range("A" & LR2).Resize(1, 6).Interior.Color = vbRed
            .Range("A" & LR2) = LR2 - 1
            'adjust column D formatting
            .Range("D" & LR2).BorderAround xlContinuous, xlThin
            .Range("D" & LR2).HorizontalAlignment = xlCenter
        End With
    End If
Next Cll

It all seems to work okay (or how I think you intended it), without error.

Hope this helps.



thanks john , this  is  really  excellent.
leap (rep: 40) Mar 6, '23 at 2:12 pm
Great. Thanks for selecting my Answer, Leap. 
John_Ru (rep: 4937) Mar 6, '23 at 2:16 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login