Macro to Copy and Paste into cell, as a value and based on colour?

[Jeevz_87]

Hi All,


Is there a way to create a macro that can copy the value in a cell and paste it back into the same cell based on the colour?

I know there has been a lot of posts around this but I feel that this request is different in the following ways;

1) I am trying to take the returned value from a calculated cell and paste it back into the same cell to 'freeze' the value in place
2) The macro should be able to do loop through a selected range (CZ:EI) and carry out the above based on the colour which is given via conditional formatting (FFFFFF/darkgrey).

The idea behind this is, the previous months data will be 'frozen' as values only (not formulas) due to the grey colour allotted by the conditional formatting, leaving future reporting months available for editing/modification. This will allow the user to save new versions of this report and update the data in the back without changing any of the figures from the previous months.

Is this possible to do?

Many thanks,


Jeevz

 

[Micron]

What is wrong with the other solutions you've looked at? The few I visited seem to do what you want, so perhaps I'm not understanding what that is.
Perhaps you can modify this to suit. It's not clear if your range is static because you posted CZ:EI or if it is supposed to be only what is selected at the time. I went with a static range for testing. If not selected but not static either, you could modify to return the last used row in either column and use that as the basis for a counting loop or concatenate that number in place of where I used 16. If selected only, there are lots of examples posted about how to loop over a selected range.
You would have to know what your colour value is for the cf being tested.

Sub testForCF()
Dim rng As Range

For Each rng In Range("A2:B16")
'Debug.Print rng.Value 'enable/disable for testing
     If rng.DisplayFormat.Interior.Color = 5296274 Then rng = rng.Value
Next

End Sub

 

[Jeevz_87]

Hi Micron,


Thanks for responding back!

I think it was probably how I was interpreting the code as Im still fairly new to VBA.

In terms of the range, it starts at CZ11 down to the bottom row of the table at column EI (this will grow in size as data is entered).

How would the code look in this case factoring in the above?

 

[Micron]

There are a gazillion posts for determining the last row in a range & I like it better when the OP does a bit of research.
Since I figured you might need to stop other code from running when modifying cell values, I'll incorporate an error handler and give you what I think should work for determining the last row in EI.

Sub testForCF()
Dim rng As Range
Dim lRow As Integer

Application.EnableEvents = False
On Error GoTo errHandler
lRow = Cells(Rows.count, "EI").End(xlUp).Row
For Each rng In Range("CZ11:EI" & lRow)
  If rng.DisplayFormat.Interior.Color = 5296274 Then rng = rng.Value
Next

exitHere:
Application.EnableEvents = True
Exit Sub

errHandler:
Msgbox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

If code raises an error when events have been disabled, they remain disabled for the entire session so that is the reason for the additional code here. If you'd rather the code ignored errors you'd need to modify (look up error handling and how to use Resume Next). Alternatively, don't worry about error handling or disabling events:

Sub testForCF()
Dim rng As Range
Dim lRow As Integer

lRow = Cells(Rows.count, "EI").End(xlUp).Row
For Each rng In Range("CZ11:EI" & lRow)
  If rng.DisplayFormat.Interior.Color = 5296274 Then rng = rng.Value
Next

End Sub