COPYING A CELL VALUE TO ANOTHER CELL WITH SAME FORMAT WITH FORMULA

[annadinesh]

Hi Team

I want to copy cell value of C23 & C24 to A2 based on Date via formula =IF(TODAY()-G6>730,C23,C24)

but the value comes on G6 should as same as in C23 & C24 format.


Please help


Dinesh Saha
9932022569

 

[Siddharth Rout]

You cannot copy formats using an excel formula. You need to use VBA for that. is this what you are trying?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
    
    '~~> Change to relevant sheet
    Set ws = Sheet1
    
    With ws
        If Date - CDate(.Range("G6").Value2) > 730 Then
            Set rng = .Range("C23")
        Else
            Set rng = .Range("C24")
        End If
        
        rng.Copy
           
        With .Range("A2")
            .PasteSpecial Paste:=xlPasteValues
            .PasteSpecial Paste:=xlPasteFormats
        End With
    End With
End Sub

 

[annadinesh]

-minunderstood

??

 

[annadinesh]

You cannot copy formats using an excel formula. You need to use VBA for that. is this what you are trying?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
   
    '~~> Change to relevant sheet
    Set ws = Sheet1
   
    With ws
        If Date - CDate(.Range("G6").Value2) > 730 Then
            Set rng = .Range("C23")
        Else
            Set rng = .Range("C24")
        End If
       
        rng.Copy
          
        With .Range("A2")
            .PasteSpecial Paste:=xlPasteValues
            .PasteSpecial Paste:=xlPasteFormats
        End With
    End With
End Sub

where to put the code in module or same worksheet

 

[Siddharth Rout]

In a module.

where to put the code in module or same worksheet

In a module.

 

[annadinesh]

In a module.


In a module.

Thanks its working , But Same format is not copying, Like A line is in BOLD and A line is in RED Colour, and G6 is merge cell of 4 cells, the value is copying in all 4 cells

Please help

 

[Siddharth Rout]

Thanks its working , But Same format is not copying, Like A line is in BOLD and A line is in RED Colour, and G6 is merge cell of 4 cells, the value is copying in all 4 cells

Please help

Did you change anything in the code because I am not copying G6.

 

[annadinesh]

Did you change anything in the code because I am not copying G6.

the data is copying from C23 & C24 , but please check the attached image file i have already attached there ia paragraph in which a line is in BOLD and a Line is in RED Colour, is there any formula to copy the same format to another as per your code, your code is working fine, and A2 is a merged cell of 6 cells from A2 to F2, the value of C23 is copying to all 6 cells instead of A2 Only

 

[Siddharth Rout]

the data is copying from C23 & C24 , but please check the attached image file i have already attached there ia paragraph in which a line is in BOLD and a Line is in RED Colour, is there any formula to copy the same format to another as per your code, your code is working fine, and A2 is a merged cell of 6 cells from A2 to F2, the value of C23 is copying to all 6 cells instead of A2 Only

I believe, C23 and C24 is also merged? Else you would not have been able to copy from a single cell to a merged cell using the code that I provided? Would it be possible to see your Excel file so that I can give you a more accurate solution?

 

[annadinesh]

Yess BOSS u r right C23 and C24 are Merged , Copy Problem Solved, But Please help me with the Format of C23 and C24 value