• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro needed to perform If function and formatting

W

wnorrick

Member
I have attached a snipet (the spreadsheets will range from 300 rows to 3500 rows) from a spreadsheet that I need to have vba code in a macro that will perform the formulas in the highlighted cells. It will need to insert the three blank columns, run the formulas, highlight the columns and also total at the bottom of those columns. I am going to have to do this on 125 spreadsheets so it would be very helpful if I have a Macro to run as I pull each one up. Any help will be appreciated. Thank you.
 

Attachments

Check with it.

Code: 
Option Explicit

Sub test()
Dim lrow As Long
Application.ScreenUpdating = False
lrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Columns("H:J").Insert
[H2:J2] = Array("New Potential Rent", "New Vacancy Loss", "Vacancy Adjustments")

Range("H5:H" & lrow) = "=IF(AND(F5>0,F5<29),D5-E5,D5)"
Range("I5:I" & lrow) = "=IF(AND(F5>0,F5<29),0,G5)"
Range("J5:J" & lrow) = "=IF(AND(F5>0,F5<29),E5,0)"

Range("H" & lrow + 1) = Application.Sum(Range("H5:H" & lrow))
Range("I" & lrow + 1) = Application.Sum(Range("I5:I" & lrow))
Range("J" & lrow + 1) = Application.Sum(Range("J5:J" & lrow))
Range("H5:J" & lrow).Interior.Color = 65535
'Columns("H:J").Interior.Color = 65535
Application.ScreenUpdating = True
End Sub
 
You must log in or register to reply here.
Back
Top