Hello People,
Just in case any is actually working on a solution to this rather urgent
problem of mine, I have updated the vb_macro file with the updated
conditions as I stated in my earlier post. I have added the POS/NEG
conditions (outside bracket elements) as well as the VPOS/VNEG/PPOS/PNEG
conditions.
My grateful thanks for any assistance or contributions.
--
FORUM RULES (925+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
Sub AutoFit_Format()
Rows("1:1").Select
With Selection.Interior
.ColorIndex = 16
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Range("A2").Select
Cells.Select
Selection.HorizontalAlignment = xlRight
Cells.EntireColumn.AutoFit
' Formats cells to 2 Decimal places
Range("A1", Selection.End(xlToRight)).Select
iCols = Selection.Columns.Count
For ic = 1 To iCols
If InStr(Selection.Item(1, ic).Value, "SUPP") = 1 Then
Selection.EntireColumn(ic).NumberFormat = "0.00"
ElseIf InStr(Selection.Item(1, ic).Value, "IM") = 1 Then
Selection.EntireColumn(ic).NumberFormat = "0.00"
ElseIf InStr(Selection.Item(1, ic).Value, "LIFT") = 1 Then
Selection.EntireColumn(ic).NumberFormat = "0.00"
ElseIf InStr(Selection.Item(1, ic).Value, "PG") = 1 Then
Selection.EntireColumn(ic).NumberFormat = "0.00"
ElseIf InStr(Selection.Item(1, ic).Value, "DIFF") >= 1 Then
Selection.EntireColumn(ic).NumberFormat = "0.00"
ElseIf InStr(Selection.Item(1, ic).Value, "CONF") >= 1 Then
Selection.EntireColumn(ic).NumberFormat = "0.00"
ElseIf InStr(Selection.Item(1, ic).Value, "_CC") > 1 Then
Selection.EntireColumn(ic).NumberFormat = "0.00"
ElseIf InStr(Selection.Item(1, ic).Value, "AVG") = 1 Then
Selection.EntireColumn(ic).NumberFormat = "0.00"
ElseIf InStr(Selection.Item(1, ic).Value, "MMRANK") = 1 Then
Selection.EntireColumn(ic).NumberFormat = "0.00"
End If
Next ic
Range("A2").Select
ActiveWindow.FreezePanes = True
End Sub
Sub FillEmpty()
' Fill in empty cells with dash: "-"
' David McRitchie http://www.mvps.org/dmcritchie/excel/fillempt.htm
Dim WithWhat As Variant
Dim Del_Char As Integer
Range("A2").Select
Del_Char = Len(Selection.End(xlDown).Select)
If Del_Char = 4 Then
Selection.ClearContents
End If
Range("A2").Select
Selection.CurrentRegion.Select
iRows = Selection.Rows.Count
iColumns = Selection.Columns.Count
WithWhat = "-"
For iC = 1 To iColumns
For iR = 1 To iRows
If Selection.Item(iR, iC).Value = "" Then
Selection.Item(iR, iC).Value = WithWhat
End If
Next iR
Next iC
Range("A2").Select
End Sub
Sub Color_Today_Cyan()
' Color Entire Row Red if Prev_date is Today
Dim str_Date As Date
str_Date = Format("09/16/2011", "Short Date")
Selection.CurrentRegion.Select
iRows = Selection.Rows.Count
iColumns = Selection.Columns.Count
For iC = 18 To 18
For iR = 2 To iRows
If Selection.Item(iR, iC).Value = str_Date Then
Selection.EntireRow(iR).Interior.ColorIndex = 28
End If
Next iR
Next iC
Range("A2").Select
End Sub
Sub Color_Active_Yellow()
' Color cells Yellow if Active
Dim CellVal As Variant
Selection.CurrentRegion.Select
iRows = Selection.Rows.Count
iColumns = Selection.Columns.Count
For iC = 1 To iColumns
For iR = 1 To iRows
Select Case Selection.Item(iR, iC).Value
Case "AA_NEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "AAPL_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "AIG_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "AXP_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "BA_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "BAC_NEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "BRKB_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "C_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "CAT_NEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "COMPX_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "CSCO_NEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "CVX_NEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "DD_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "DIA_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "DIS_NEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "DJIA_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "GE_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "GLD_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "GM_NEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "GOOG_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "HD_NEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "HON_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "HPQ_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "IBM_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "INTC_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "JNJ_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "JPM_NEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "KFT_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "KO_NEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "MCD_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "MMM_NEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "MO_NEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "MRK_ZERO"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "MSFT_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "PFE_NEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "PG_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "SPXX_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "SPY_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "T_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "TLT_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "TRV_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "USO_NEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "UTX_NEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "VZ_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "WMT_NEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "XOM_POS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "AA_PNEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "AAPL_VPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "AIG_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "AXP_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "BA_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "BAC_VNEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "BRKB_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "C_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "CAT_VNEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "COMPX_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "CSCO_PNEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "CVX_PNEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "DD_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "DIA_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "DIS_PNEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "DJIA_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "GE_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "GLD_VPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "GM_PNEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "GOOG_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "HD_PNEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "HON_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "HPQ_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "IBM_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "INTC_VPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "JNJ_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "JPM_VNEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "KFT_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "KO_PNEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "MCD_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "MMM_VNEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "MO_PNEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "MRK_ZERO"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "MSFT_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "PFE_VNEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "PG_VPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "SPXX_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "SPY_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "T_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "TLT_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "TRV_VPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "USO_VNEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "UTX_PNEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "VZ_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "WMT_PNEG"
Selection.Item(iR, iC).Interior.ColorIndex = 6
Case "XOM_PPOS"
Selection.Item(iR, iC).Interior.ColorIndex = 6
End Select
Next iR
Next iC
Range("A2").Select
End Sub
Sub Color_InBody_Green()
' Color Element Green if in Body
Dim str_Body As Variant
Dim str_Element As Variant
Dim cell_clr As Integer
Selection.CurrentRegion.Select
iRows = Selection.Rows.Count
iColumns = Selection.Columns.Count
For iC = 1 To iColumns
For iR = 1 To iRows
str_Body = Selection.Item(iR, 13).Value
str_Element = Selection.Item(iR, iC).Value
If Len(str_Element) >= 5 Then
If InStr(str_Body, str_Element) Then
cell_clr = Selection.Item(iR, 13).Interior.ColorIndex
Selection.Item(iR, iC).Interior.ColorIndex = 4
Selection.Item(iR, 13).Interior.ColorIndex = cell_clr
End If
End If
Next iR
Next iC
Range("A2").Select
End Sub
