Adjust value of a cell based on the formatting of another using VBA

[Mr2017]

Hi

Simple question about VBA.

I have two sheets - Sheet 1 and Sheet 2.

Sheet 1 has the values 1, 2, and 3 in cells A1, A2, and A3, respectively.

Sheet 1 also has the values 4, 5, and 6 in cells B1, B2, and B3, respectively.

Cells A1 and B3 in Sheet 1 are highlighted in yellow. So Sheet 1 looks like this:

1​	4​
2​	5​
3​	6​

Sheet 2 has the number 0 in cells A1, A2, A3, B1, B2, and B3. Like this:

0​	0​
0​	0​
0​	0​

Question:

Is it possible to get VBA to adjust the values of cells in Sheet 2 based ONLY on the values of the HIGHLIGHTED cells in Sheet 1?

ie the values in the cells in Sheet 2 would become 1 in cell A1 and 6 in cell B3, because those cells are highlighted yellow in Sheet 1? And the formatting would also change so that the cells that have changed in Sheet 2 also become yellow?

Please let me know if you'd like me to clarify the question.

Thanks

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I'm not sure your question is entirely clear but try this with a copy of your workbook to see if it is heading in the right direction.

Sub Adjust_Values()
  Dim r As Range
  
  For Each r In Sheets("Sheet1").Range("A1:B3")
    If r.Interior.Color <> 16777215 Then r.Copy Destination:=Sheets("Sheet2").Range(r.Address)
  Next r
End Sub

 

[Mr2017]

Awesome! That worked!! Thanks Peter!

 

[Rick Rothstein]

Peter's code worked for you? If it did, then your thread title is not accurate. It says the cells are Conditionally Formatted which I figure means the color you are referring is coming from that Conditional Format. If that is the case, then Peter's code, as written, would not be able to see that color... his code (which can be modified), can only see colors that are manually (includes macros) applied. Please clarify. Also, if the cells have some different Conditional Formatting, but the colors are applied manually (so Peter's code would be able to see them), his Copy command will transfer the Conditional Formatting to the cells on Sheet2. Is that what you wanted to happen? Again, please clarify.

 

[Mr2017]

Hi Rick

Peter's code worked as intended.

Apologies if the thread title isn't accurate.

I just wanted to change the value of cells in Sheet 2 IF the equivalent cells in Sheet 1 were highlighted.

So when I said based on the conditional formatting of another cell, I simply meant the formatting of another cell.

I'm happy for you to amend the thread title and take out the word 'conditional,' if that's possible?

But I'm glad that Peter's code works.

 

[Rick Rothstein]

I don't have the power to change thread title. I am not sure, but you should see if the system will let you, as the creator, change it.

As for my comments... I just wanted to make sure you were actually getting what you wanted and didn't think it worked because of some shortcut testing process you might have used.

 

[Mr2017]

I have tried to change a thread title in the past, but the site wouldn't let me.

Yes, the code he posted worked.

Thanks

 

[Mr2017]

Hi Guys

Is there a way to make the code dynamic?

So if the number of rows in both Sheets changed to 10 rows, and more cells were highlighted yellow in Sheet 1, then the values from all the highlighted cells in Sheet 1 would get transferred over to the corresponding cells in Sheet 2?

 

[Peter_SSs]

I saw the "conditional formatting" in the thread title but must admit forgot about when writing the code. Looks like a lucky mistake was made.

I'm happy for you to amend the thread title and take out the word 'conditional,' if that's possible?

I have done that for you.

Is there a way to make the code dynamic?

So if the number of rows in both Sheets changed to 10 rows, and more cells were highlighted yellow in Sheet 1, then the values from all the highlighted cells in Sheet 1 would get transferred over to the corresponding cells in Sheet 2?

Try this version

Sub Adjust_Values_v2()
  Dim r As Range

  For Each r In Sheets("Sheet1").Range("A1", Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp))
    If r.Interior.Color <> 16777215 Then r.Copy Destination:=Sheets("Sheet2").Range(r.Address)
  Next r
End Sub

Could a cell in Sheet1 have its yellow colour removed? If so, does the colour or cell value also need to be removed from the corresponding cell in Sheet2? If so, please give details about what should happen to both the colour and value of the Sheet2 cell.

 

[Mr2017]

Awesome!!

Thanks Peter!!

Both the fixed and dynamic versions worked!

Thanks again for your help!

It's greatly appreciated!