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



    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:

    Set the pattern to yellow.


    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


    See if this is what you want:

    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
            Target.EntireRow.Interior.ColorIndex = xlNone
        End If
    End Sub


    Re: Filter code

    Hey Kim.

    Change this line:


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


    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.

    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.


    The countif function has the following arguments:

    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.
    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

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


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


    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?