Formulas into values based on cell

**Reayn** · 11-29-2010, 02:59 PM

Hi, I am trying to convert a set of formulas into values based on a 'date' value in cell A2 i.e. when A2='November 30, 2010' then all formulas in range A4:G22 become values. Is this possible? Also, FYI,
- The sheet/formulas are protected
- I am using Excel 2007
- The spreadsheet is macro-enabled completely

Any help would be appreciated!

UPDATE: Hi, thank you very much for the code!! although there was one small change: I would like to convert the range to values based on a specific text entry i.e. when A2='Actuals' then cells become values. Can you clarify on how to change the code for this?

Thanks,

Reayn.

**Bob Phillips** · 11-29-2010, 03:17 PM

Something along the lines of

With Worksheets("Sheet1")

    Select Case .Range("A2").Value

        Case DateSerial(2010, 11, 30):  .Range("A4:G22").Value = .Range("A4:G22").Value
                
        Case DateSerial(2010, 12, 12):  .Range("M2:N5").Value = .Range("M2:N5").Value
        
        'etc.
    End Select
End With

**Leith Ross** · 11-29-2010, 03:34 PM

Hello Reayn,

Welcome to the Forum!

If you want the cells to change whenever cell "A2" contains a date, not just today's date, then this macro will do that. You will need to change the password in the macro below from "password" to yours.

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim pwd As String
  Dim Rng As Range
  
    If Target.Cells.Count > 1 Or Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
    
    If Not IsDate(Target) Then Exit Sub
    
    pwd = "password"
    Set Rng = Range("A4:G22")
    
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

      ActiveSheet.Unprotect pwd
        Rng.Value = Rng.Value
      ActiveSheet.Protect pwd
      
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

End Sub

How to Save a Worksheet Event Macro

Copy the macro using CTRL+C keys.
Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
Left Click on View Code in the pop up menu.
Paste the macro code using CTRL+V
Make any custom changes to the macro if needed at this time.
Save the macro in your Workbook using CTRL+S