I have 5 cells in different Sheets, I need that if one of those cells change, the other ones change to the same vale. e.j. if i write in cell A1 "red", then A2, A3, B1, B2, B3 change to "red". But if then, I write "blue" in B2, then A1, A2, A3, B1, B3 change to "blue"

Thanks

You cannot do what you want to do with formulas in those cells. I'll use 2 cells as an example to show why it won't work with formulas in the target cells... A1 ---> Manual Entry

B1 ---> =A1A manual entry in A1 will show in B1. That's good.

A manual entry in B1 will replace the formula in B1. That's bad.Now you are left with 2 manual entry cells.

I see 2 choices:

1 - Have another cell outside of your range as the "manual entry cell" for the entire range. Then set all cells in your range equal to that cell.

2 - Use a macro which monitors all cells in the range and if one of them changes, change them all.

I could offer the code for that macro, but I'd need to know a little bit more about what you mean by "5 cells in different Sheets"

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

The idea from the beginning was a Macro, I know that with formulas would be impossible. To explain a litlle of what I'm doing I have a workbook with with 8 Sheets, 5 of them consult data based on the other 3, specifically calculations based on the date. For this I want to be able that in every sheet I have, if I change the date, the other 8 sheets change as well and Excel can calculate based on the new date. For these I need that if I change the date on Sheet 1, Sheet 6 changes. But if i go to sheet 2 and change the date, then sheet 6 changes to the new date and calculate accordingly

Well, without specifics as to sheet names/number and cell references the best I can do is offer some generic code. If the following macro is pasted into the sheet module for allsheets, the specified range will reflect a change to any cell in that range on any sheet.I know it is not

exactlywhat you asked for (e.g. 5 out 8 sheets, etc.) but it's the best I can offer without more details.Private Sub Worksheet_Change(ByVal Target As Range) 'Determine if change was made to specified range If Target.Cells.Count = 1 Then If Not Intersect(Target, Range("A2, A3, B1, B2, B3")) Is Nothing Then Application.EnableEvents = False 'Loop through Sheets setting all cells equal to the changed cell For sht = 1 To Sheets.Count Sheets(sht).Range("A2, A3, B1, B2, B3") = Target Next Application.EnableEvents = True End If End If End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History