If created a new line, gain a different collor & automatically a specific collumn cell gains a text

analee

New Member
Joined
Jul 29, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Good Afternoon

I'm having issues with the following:
I want that my excel sheet, every time that I insert a row (the row can be inserted in a different part of the table present in the sheet) automatically it gains a collor (like light green).
Likewise, when creating this new line(s), I have a collumn that asks "Was created a new row?". For this new lines, I wanted that the respective cell(s) of this collumn "Was created a new row?" gains automatically the text "YES".
Could you please help me?

Many thanks
Kind Regards
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Welcome to the Board!

If I understand you correctly, it sounds to me like you could just use Conditional Formatting on your sheet to do this.
For example, if this "Yes/No" field was in column A, then you could just have a Conditional Formatting formula like this (applied to all of the columns you want to highlight):
Excel Formula:
=$A1="Yes"
and then select your light-green formatting option.
 
Upvote 0
Hi there
No, unfortunatelly. I believe that probably will be needed a macro for that.
The goal here is to when add a new row, automatically the collumn AB changes for YES automatically.
I already get to resolve the collor challenge but I have still the challenge of, when creating a row, change the collumn cell result automatically.
Many thanks
Stay safe :)
 
Upvote 0
I think your best bet would be to protect the sheet, so that they are unable to insert rows in the normal manner (see: Inserting and Deleting Rows in a Protected Worksheet).

Then, create another procedure specifically used to insert rows that will:
1. Unprotect the sheet
2. Insert the new row
3. Populate your cell value/row coloring
4. Re-protect the sheet

So they would need to run this VBA code whenever they want to insert a new row.

Here is a link which shows how to protect/unprotect your sheet in VBA code:
 
Upvote 0
Hello
Many thanks for your feedback. Sounds a good idea.
I just have one more point: I need people to fill the info missing for the others collumns in the new row created & in the existing rows.
It is possible only to block a bunch of collumns and not the all sheet?

Many thanks again
Stay safe
 
Upvote 0
Upvote 0
When you protect a sheet, the only cells actually protected are the ones that are "locked".
So if you unlock the cells in other areas, they should not be affected by the protection.
See: https://www.co.lucas.oh.us/DocumentCenter/View/44910/How-to-protect-only-certain-Cells-In-Excel-

By default, all the cells in a sheet are "Locked" (which doesn't do anything until you protect the sheet).
So typically, you need to "unlock" the cells you do not want affected.
Many, many thanks :)
Stay safe
 
Upvote 0
You are welcome.
Glad I was able to help!
:)
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top