Posts by luceze

    Re: take slashes out of dates


    If your value is truly a date value (date serial) the slashes are actually just formatting. Try formatting your cell as such


    mmddyyyy


    HTH,
    Eric

    Re: Highlight a row when "s" is entered


    Norie's absolutely right.


    Select column A, go to format\conditional formatting


    Change "Cell Value Is" to "Formula Is".


    Enter this as your formula:
    =FIND(A1,"s")>0


    Set the pattern to yellow.


    HTH,

    Re: Macro Assistance: When Value of K1393=0, Prompt for Macro


    This code will work. You might need to change some cell references. I wasn't sure which range you wanted to copy over.



    Just right click on your sheet tab and select view code. Paste the code above in the code window.

    Re: Highlight a row when "s" is entered


    Hello,


    See if this is what you want:


    Code
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        If Target.Column <> 1 Then Exit Sub
        If InStr(1, Target.Value, "s") Then
            Target.EntireRow.Interior.ColorIndex = 27
                Else
            Target.EntireRow.Interior.ColorIndex = xlNone
        End If
    End Sub


    HTH,

    Re: Filter code


    Hey Kim.


    Change this line:
    ActiveSheet.ShowAllData


    to


    If Sheets("Portfolio_Filter").FilterMode = True Then
    Sheets("Portfolio_Filter").ShowAllData
    End If


    HTH,

    Re: Highlighting Rows based on Value


    Hey Taylor,


    Conditional Formatting is the way to go here.


    Select all of the cells in your worksheet.


    Go to Format/Conditional Formatting.


    Change "Cell Value Is" to "Formula Is"


    Paste this in the box to the right.


    =IF($A1<>"",IF($B1=4,TRUE,IF($B1=6,TRUE,FALSE)),FALSE)
    You will need to change the reference to column B to the column that contains the data you want to test.


    Click the format button and on the patterns tab select the color yellow.


    Click OK.




    HTH,

    The countif function has the following arguments:
    =countif(Range,Criteria)


    Range is the list of values that contains the value that you are trying to count. Criteria is the value that you are trying to count.


    Second question:


    When you drag a formula down the cell references are automatically adjusted to the current row and/or column.


    If you state the references as absolute the references will not adjust.
    Example:
    =countif(I1:I20,I20)
    When you drag this formula down any reference not enclosed in a "$" will change. If you drag it down one row it will change to
    =countif(I2:I21,I21)


    To properly use the function that Mhabib suggested then create absolute references in the formula.


    =countif($I$1:$I$20,I1)


    When you drag this formula down the range argument reference will stay static but the criteria range will adjust as you drag it down.


    HTH,
    Eric

    Here's a neat little snippet of code that I picked up some time ago that will do what you want. Not sure who to give the credit to.



    P.S. The strikethrough W's are supposed to be backslashes. Anyone know how to keep the compiler from converting them?
    HTH,
    Eric