-1

the yellow highighted is where the data is entered lets say cell b2 is data entry.. and i2 to AD2 are the cells in which formulas are suppose to be set.

i need a vba code which identifies b2 = any amount/symbol if its true, I2 to ad2 should insert this if formula [[IF($I$1=D2,G2,"")]

This should be applied in all the rows

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Malcolm Dias
  • 15
  • 4

1 Answers1

1

Please place this in your worksheet's module.

It checks, whether cell B2 is changed and contains something, and then places the formula in whole range, starting at I2:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim RelevantArea As Range
    Dim lastRow As Long

    Set RelevantArea = Intersect(Target, Me.Range("B2"))
    If Not RelevantArea Is Nothing Then
        If Len(Target.Value2) > 0 Then
            ' find the last used row, e. g. in column 9:
            lastRow = Me.Cells(Me.Rows.Count, 9).End(xlUp).Row
            Application.EnableEvents = False
            Me.Range("I2:AD" & lastRow).Formula = "=IF(I$1=$D2,$G2,"""")"
            Application.EnableEvents = True
        End If
    End If
End Sub

The formula is inserted into the range like you would get it, if you copy the formula of the first cell (here: I2) to the rest of the range. I changed the formula a little, assuming you wanted it like that.

By following you get it for the changed row only, i. e. if you paste into e. g. B5:B9, it works for rows 5 to 9.
You can use the A1- or R1C1-notation to adapt the formula to your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MonitoredArea As Range
    Dim CurrentRow As Long
    Dim CurrentCell As Range

    Set MonitoredArea = Intersect(Target, Me.Range("B:B"))
    If Not MonitoredArea Is Nothing Then
        For Each CurrentCell In MonitoredArea.Cells
            If Len(CurrentCell.Value2) > 0 Then
                CurrentRow = CurrentCell.Row
                Application.EnableEvents = False
                With Me.Range(Me.Cells(CurrentRow, "I"), Me.Cells(CurrentRow, "AD"))
                    .Formula = "=IF(I$1=$D" & CurrentRow & ",$G" & CurrentRow & ","""")"
                    '.FormulaR1C1 = "=IF(R1C=RC4,RC7,"""")"

                    Dim i As Integer
                    For i = xlEdgeLeft To xlInsideHorizontal  ' all borders
                        With .Borders(i)
                            .LineStyle = xlContinuous
                            .Weight = xlThin
                            .Color = RGB(0, 0, 0)
                            .TintAndShade = 0
                        End With
                    Next i

                End With
                Application.EnableEvents = True
            End If
        Next CurrentCell
    End If
End Sub
Asger
  • 3,822
  • 3
  • 12
  • 37
  • If you copied it in your worksheet‘s module (not ThisWorkbook) and write something into cell B2, some new formula should be seen, starting in cells I2:AD2 and below. – Asger Feb 11 '19 at 06:38
  • it worked but i want this code to work on each row seperately for e.g if b2 contains a number/symbol then i2 to ad2 should insert the formula same as when b3 contains a number/symbol then i3 to ad3 should insert the number and goes on unless column b rows doesnt contain any thing the rest of the sheet should remain empty thanks – Malcolm Dias Feb 11 '19 at 07:18
  • can u add a code of "all borders" in this same code. like as the formula is inserted in the same range of cell that is i2:ad2 cell should aplly all borders too – Malcolm Dias Feb 13 '19 at 07:45
  • @Malcolm I wrote the code for you, I corrected your formula, I added code for the borders and you already said "it worked thanks alot". Now I kindly ask you again to mark my answer as answer. If you don't know how, please refer to https://stackoverflow.com/help/someone-answers. – Asger Feb 14 '19 at 21:15