Conditional balance sheet changing

D

Dennis Reynolds

New Member
Joined
Jul 12, 2011
Messages
9
Hey everyone,

I am new to VBA and have to write a little here and there for work as an intern. I've been trying to write something that for a balance sheet in which the liability side changes dependent on the type of entry (Life/health or Propert/casualty). The sheet has a title which is linked to a drop down menu which gives it an individual header the last two letters being either PC or LH. Since the balance sheet names for PC and LH are different they have to be entered conditionally for them to make sense. I have only been trying to write the LH code as it is longer (more items in the liability balance sheet side) and I have been having trouble. I'm not sure what type of format to use (case, if then, etc) and also I can't remember or figure out how to properly reference the sheet header.
This is my code as of right now. It is messy and wrong so please go easy on me :). I also have to implement different formatting for respective row titles (i.e Replicating Portfolio Title is different than interest rate derivative, etc)

Thank you very much to anyone who replies

Sub BS_Change()
Dim segment As String

Sheets("Limit_Report").Right(Range("B2").Value, 2) = segment
Select Case segment
Case LH

With LH.Font
.Name = "Arial"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 49
End With

Range("E33").Value = "Liability"
With LH.Font
.FontStyle = "Fett"
.Size = 11
End With

LH.Borders(xlDiagonalDown).LineStyle = xlNone
LH.Borders(xlDiagonalUp).LineStyle = xlNone
With LH.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 49
End With
With LH.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 49
End With
With LH.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
LH.Borders(xlEdgeRight).LineStyle = xlNone

Range("E34").Value = "Replicating Portfolio"
With LH.Font
.FontStyle = "Fett"
.Size = 11
End With

With LH
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 2
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

LH.Borders(xlDiagonalDown).LineStyle = xlNone
LH.Borders(xlDiagonalUp).LineStyle = xlNone
With LH.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 49
End With
With LH.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
With LH.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
LH.Borders(xlEdgeRight).LineStyle = xlNone


Range("E35").Value = "Securities"
With LH.Font
.FontStyle = "Normal"
.Size = 10
End With

With LH
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 3
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

LH.Borders(xlDiagonalDown).LineStyle = xlNone
LH.Borders(xlDiagonalUp).LineStyle = xlNone
With LH.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 49
End With
With LH.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
With LH.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
LH.Borders(xlEdgeRight).LineStyle = xlNone


Range("E36").Value = "Interest Rate Securities"
With LH.Font
.FontStyle = "Normal"
.Size = 10
End With

With LH
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 3
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

LH.Borders(xlDiagonalDown).LineStyle = xlNone
LH.Borders(xlDiagonalUp).LineStyle = xlNone
With LH.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 49
End With
With LH.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
With LH.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With

Range("E37").Value = "Equities"
With LH.Font
.FontStyle = "Normal"
.Size = 10
End With

With LH
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 3
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

LH.Borders(xlDiagonalDown).LineStyle = xlNone
LH.Borders(xlDiagonalUp).LineStyle = xlNone
With LH.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 49
End With
With LH.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
With LH.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With

Range("E38").Value = "Alternative Investments"
With LH.Font
.FontStyle = "Normal"
.Size = 10
End With

With LH
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 3
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

LH.Borders(xlDiagonalDown).LineStyle = xlNone
LH.Borders(xlDiagonalUp).LineStyle = xlNone
With LH.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 49
End With
With LH.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
With LH.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
Range("E39").Value = "Real Estate"
With LH.Font
.FontStyle = "Normal"
.Size = 10
End With

With LH
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 3
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

LH.Borders(xlDiagonalDown).LineStyle = xlNone
LH.Borders(xlDiagonalUp).LineStyle = xlNone
With LH.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 49
End With
With LH.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
With LH.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
Range("E40").Value = "Derivatives"
With LH.Font
.FontStyle = "Normal"
.Size = 10
End With

With LH
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 3
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

LH.Borders(xlDiagonalDown).LineStyle = xlNone
LH.Borders(xlDiagonalUp).LineStyle = xlNone
With LH.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 49
End With
With LH.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
With LH.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
Range("E41").Value = "Interest Rate Derivative"
With LH.Font
.FontStyle = "Normal"
.Size = 10
End With

With LH
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 3
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

LH.Borders(xlDiagonalDown).LineStyle = xlNone
LH.Borders(xlDiagonalUp).LineStyle = xlNone
With LH.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 49
End With
With LH.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
With LH.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
Range("E42").Value = "Equity Derivative"
With LH.Font
.FontStyle = "Normal"
.Size = 10
End With

With LH
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 3
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

LH.Borders(xlDiagonalDown).LineStyle = xlNone
LH.Borders(xlDiagonalUp).LineStyle = xlNone
With LH.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 49
End With
With LH.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
With LH.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
Range("E43").Value = "Alternative Investment Derivatives"
With LH.Font
.FontStyle = "Normal"
.Size = 10
End With

With LH
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 3
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

LH.Borders(xlDiagonalDown).LineStyle = xlNone
LH.Borders(xlDiagonalUp).LineStyle = xlNone
With LH.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 49
End With
With LH.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
With LH.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
Range("E44").Value = "Real Estate Derivatives"
With LH.Font
.FontStyle = "Normal"
.Size = 10
End With

With LH
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 3
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

LH.Borders(xlDiagonalDown).LineStyle = xlNone
LH.Borders(xlDiagonalUp).LineStyle = xlNone
With LH.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 49
End With
With LH.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
With LH.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
Range("E33").Value = "Cash"
With LH.Font
.FontStyle = "Normal"
.Size = 10
End With

With LH
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 3
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

LH.Borders(xlDiagonalDown).LineStyle = xlNone
LH.Borders(xlDiagonalUp).LineStyle = xlNone
With LH.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 49
End With
With LH.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
With LH.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
Range("E33").Value = "PV of reserves"
With LH.Font
.FontStyle = "Normal"
.Size = 10
End With

With LH
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 3
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

LH.Borders(xlDiagonalDown).LineStyle = xlNone
LH.Borders(xlDiagonalUp).LineStyle = xlNone
With LH.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 49
End With
With LH.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
With LH.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
Range("E33").Value = "MVM"
With LH.Font
.FontStyle = "Normal"
.Size = 10
End With

With LH
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 3
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

LH.Borders(xlDiagonalDown).LineStyle = xlNone
LH.Borders(xlDiagonalUp).LineStyle = xlNone
With LH.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 49
End With
With LH.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
With LH.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
Range("E33").Value = "Other Liabilities"
With LH.Font
.FontStyle = "Normal"
.Size = 10
End With

With LH
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 3
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

LH.Borders(xlDiagonalDown).LineStyle = xlNone
LH.Borders(xlDiagonalUp).LineStyle = xlNone
With LH.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 49
End With
With LH.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
With LH.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
Range("E33").Value = "Tax"
With LH.Font
.FontStyle = "Normal"
.Size = 10
End With

With LH
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 3
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

LH.Borders(xlDiagonalDown).LineStyle = xlNone
LH.Borders(xlDiagonalUp).LineStyle = xlNone
With LH.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 49
End With
With LH.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With
With LH.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = 49
End With

End Select
End Sub
Click to expand...
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
You must log in or register to reply here.

Similar threads

K
  • Solved
formating question
Replies
3
Views
324
Micron
M
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
594
RoryA
RoryA
K
  • Solved
create a button that do actions
2 3
Replies
22
Views
1K
kerm007
K
P
  • Question Question
Issue with button not running in spread sheet only in test mode
Replies
1
Views
378
6StringJazzer
6StringJazzer
togenmoser
  • Solved
Help with VBA code error "For variable already in use"
2 3
Replies
22
Views
1K
MARK858
MARK858

Forum statistics

Threads
1,224,599
Messages
6,179,828
Members
452,946
Latest member
JoseDavid

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top