VBA code - when new row is inserted the total row should change color

A

a984sce

New member
Joined
Feb 22, 2018
Messages
3
Reaction score
0
Points
0
Hello All,
Please can I request your help on the following.
I have an excel workbook which is made of 4 worksheets. Each worksheet is made up of text, date and value information.
I am looking for a VBA code for this workbook which will perform the following checks/correction:
  1. Value in the worksheet should be only 2 decimals
  2. When the user insert's an additional row, the total's row should change color which will act as an indication for the reviewer that row's have been added.

Appreciate your help.

Thank you.
Regards
Venkat
 
re: #1
You didn't bother to acknowledge the suggestion you got towards this at MrExcel (nine months ago).

re: #2
Have you tried using the macro recorder to record yourself doing this manually to get an idea of what would be required?
Or thought about attaching a sample workbook so anyone willing to assist would know the data layout you have?
 
Hello NoS,

Thank you for your response.

re: #1
I got pulled in to support another project implementation and so I have not been able to check any of my old messages ie. both official and personal. So if you have responded, thank you and apologies for the delayed response. One request - would you be able to post this response once more so that I can go thru the same.

re: #2
tried to upload xlsx file but get error meesages 3 times.

Regards
 
a984sce said:
Hello NoS,

re: #2
please find the excel sample file attached herewith. For example if row is inserted in the Revenue Group (rows 2,3), then total revenue column should highlight in color and also the row net profit/(loss) should colored. This will act as a control check that the user has to verify the formula when row is inserted. Similar on the cost side if any row is inserted on any of the expense groups then the relevant expense group and grand total and net profit row should show up in red color.

Please also use the information in the file to format cells to have only 2 decimals (my request #1).

Appreciate your help.

Regards
Click to expand...
 

Attachments

  • Insert_row_formatting.xlsx
    12.8 KB · Views: 11
#1. depending upon what you want, either format the cells for 2 decimal places or build it into your formulas as suggested at MrE.
Somebody might be able to programmatically change the formulas as required but not me.

#2. I think this does what you're looking for. It uses an added sheet to keep track of the current number of used rows.
Alt+F8 shows the macro dialogue, there's a macro to remove the color.

Good luck with the project.
 

Attachments

  • Try_This_Insert_row_formatting.xlsm
    25.4 KB · Views: 12
You must log in or register to reply here.
Back
Top