0

I have an excel file, i need to do is, format the cell depending on another cell. If cell "S1" is having 3 decimals cell "T1" should be have the same number of decimals and it is calculated field. ex: S1 = 11.123 ---- T1 = 1.099

I need to do for the entire column.

I am able count the number of decimals on the cell by placing the code on worksheet_change. But i have no idea how to format it.

Please help in this regards. Thanks in advance

Community
  • 1
  • 1
user1049518
  • 291
  • 7
  • 13
  • 25
  • Try recording a macro and see how Excel formats a cell and then use that code :) – Siddharth Rout Jun 26 '12 at 09:19
  • Thanks Siddharth. for each cell we need to format manually. Range("A1").Numberformat = "0.000" like.... But i need to do it automatically – user1049518 Jun 26 '12 at 09:25
  • If the formatting of the "S" cells is not "general" but "Number" with specific decimals then you can record a macro to see how "Format Painter" can be used to bulk format the "T" cells. If the "S" Cell's format is set to "General" for example then you will have to loop to find the number of decimals and then format the T cells accordingly. – Siddharth Rout Jun 26 '12 at 09:30
  • were able to give any example.. Thanks for your response – user1049518 Jun 26 '12 at 09:46

1 Answers1

0

Since you wanted an example, here it is :) But please note that this is not an answer. It's just that the comments will not be able to hold all this data plus it will ruin the formatting as well. Having said that, it is almost as good as an answer as it covers almost every aspect of what you want...

My Assumptions:

  1. Data is in Sheet1
  2. Col S and Col T are identical

Logic to achieve what you want

  1. Get the last row of Col S. For example see this
  2. Loop though the cells in Col S and check the number for decimals
  3. Use .Numberformat for format the cells in Col T based on the number of decimals in respective cell in Col S

Few code snippets

Looping Through cells in Col S

For i = 1 To LastRow
    If Sheets("Sheet1").Range("S" & i) .... Then

    End If
Next i

Setting the number format of cell T in the above loop

'~~> n below is the number of decimal places that you want
Sheets("Sheet1").Range("T" & i).NumberFormat = "0." & String(n, "0")

Incorporate all these and then try to come up with a code. Let use know where you are stuck and we will take it from there.

Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • 1
    THANKS Siddarth This is worked fine for me to format the cell Sheets("Sheet1").Range("T" & i).NumberFormat = "0." & String(n, "0") Thanks a lot – user1049518 Jun 26 '12 at 10:33