VBA coding needed please

[LadyHarper]

Good Morning,

I need to write a VBA coding for a conditional formatting

Formula =$A1<>$A2 Applies to $A:$M

It's so that it adds Lines as I am adding rows when Column A is different. The problem with conditional formatting is when you insert a line, as something was missed it breaks up the formula and I need it to stay the same.

 

[DanteAmor]

Try:

Sub Macro1()
  Cells.FormatConditions.Delete
  With Range("A:M")         'Adjust the range as you wish
    .FormatConditions.Add xlExpression, , "=$A1<>$A2"
    .FormatConditions(1).Interior.Color = vbYellow
  End With
End Sub

 

[LadyHarper]

I added this in and it is not creating the lines.

 

[LadyHarper]

It's highlighting stuff, I need it to automatically add a line under the row, when the number changes in column A

I am continuously adding rows, and need it to automatically add the line, without running the marco

 

[DanteAmor]

It's highlighting stuff, I need it to automatically add a line under the row, when the number changes in column A

I need to write a VBA coding for a conditional formatting

Formula =$A1<>$A2 Applies to $A:$M

I don't understand what you need.

 

[LadyHarper]

Ok so I am adding rows, and when Column A changes to something different, I need it to add a bottom border line. Like when you create lines around a cell, it to create the line, across A to M.

and it to automatically add this line, as I work, and not have to go in and run a macro.

So if row 1, 2 and 3, all have the number 1 in column A then the line would appear after row 3, and then row 4, 5 and 6 all have the number 4 in column A, the line appears after row 6.

the above formula, $A1<>$A2 tells it to apply the formatting to $A:$M.

But I don't know how to write a VBA script to make it do it automatically, and to create the bottom border Line.

Your code highlights instead of creating the cell bottom boarder line, and I have to tell it to run the macro to do it, the code needs to be automatic,

 

[LadyHarper]

Still looking for VBA coding that can have excel do this automatically, as in the conditional formatting, if I delete or add a line, it changes the applies to, and I need it to always stay the same.

 

[DanteAmor]

The following code will be executed automatically every time you modify a cell in column A, even if you add a new row or delete one or more rows.


Put the following code in Sheet Event:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range

  Set rng = Intersect(Target, Range("A:A"))
  If Not rng Is Nothing Then
    Cells.FormatConditions.Delete
    With Range("A:M")         'Adjust the range as you wish
      .FormatConditions.Add xlExpression, , "=$A1<>$A2"
      .FormatConditions(1).Borders(xlBottom).LineStyle = xlContinuous
    End With
  End If
End Sub

Note Sheet Event:
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[LadyHarper]

Thank you thank you, this seems to work.