Posts by Dougmeister

    Is is possible to test for the existing color of a cell when applying Conditional Formatting to the text within a cell?

    Alternatively, is it possible to only change the color of the text itself and leave the original cell background? Almost like a "transparent" background color?

    (I'm applying conditional formatting to cells and the background colors are different depending on a specific set of rules)

    A cell will contain the words "GREEN", "YELLOW", or "RED". FWIW, the words will be surrounded by asterisks (e.g, "***GREEN***").

    I am trying to craft an elegant formula that will parse the cell and return "Green", "Yellow", or "Red".

    This is the formula I have so far, but it errors out if the original cell does not contain the first color ("GREEN"):


    I could add "iferror" I guess, but there may be a better way?


    Is there a more elegant solution? Thanks.

    Column A has a date. I want Column B to tell me what day of the week it is.

    If I Google something generic such as "excel vba weekday as name", multiple results are returned. I'm looking for an elegant, in-line solution involving a formula directly in the cell (no VBA modules, etc.).

    Many, many sites say that I can use a function called "WeekDayName", including MSDN:

    [FONT=&amp]WeekdayName( weekday , abbreviate , firstdayofweek )…rary/office/gg278600.aspx[/FONT]
    [FONT=&amp]Yet when I type the following line into a cell, I get an error:

    [FONT=&amp]What am I missing? The function name does not even show up when I start typing... can it ONLY be used in a VBA module?

    [/FONT]Edit: do I just format column B as "dddd"? Is that the simplest solution?

    Given: The target rows are already selected when the macro is executed
    Problem: Speed

    Was using this:


    But as the particular worksheet I am using grows longer, it takes more and more time.

    I thought I could write a short, simple, and fast routine to parse JUST the selected rows, but it took just as long (or longer) than the original.

    Is there a fast yet elegant solution?


    Re: Workbook open code doesn't execute after applying Excel 2007 self-cert on local P

    If you mean:

    Application.EnableEvents = False

    then no, I have not disabled events.

    I am not programmatically doing anything with Application.AutomationSecurity, and workplace security settings prevent me from changing macro security settings (currently set to "disabled with notification").

    I am not running the code from a keyboard shortcut involving the Shift key.

    I am copying a percentage value from Excel (via VBA) to Word. Sometimes I want it to display with 2 digits of precision to the right of the decimal point:

    wdRng.Text = Format(vValue, "Percent")

    E.g.: "Format(0.885, "Percent")" yields "88.50%"

    But when the PERCENTAGE value is ANY whole number (e.g., "100%", "85%"), I don't want it to only display ONE zero to the right of the decimal point ("100.0%", "85.0%").

    Is this best achieved by:

    If (value <> 1) then
        wdRng.Text = Format(vValue, "Percent")
        wdRng.Text = "100.0%" ' but what if it is not 100% What if it is 85%?
    End If

    That doesn't seem very elegant.

    1) Is there a way to format it such that the "else" would be using the actual value of the variable vValue instead of hard-coding "100.0%"?

    2) Is there an elegant solution that would only use a single line of code and would format vValue to 2 decimal points if the 2nd digit of precision (the hundredths spot) is not zero, but to 1 decimal place if it is a whole number? (I.e., 1st decimal place = 0, 2nd decimal place = 0, etc.)


    Re: Dynamic named range, ignoring blanks... but on different worksheet?

    After following those steps, I got the error "The Source currently evaluates to an error. Do you want to continue?"

    I will attempt to clean up the workbook and upload it soon. Thanks.

    (Edit: as a side note, I do not see "Dates" listed in the "Named Cell" ("Name Box"?) section of the worksheet...)

    Edit #2: this is what I have in the "Name Manager":


    and when I try the same code in the "Data Validation", I get:

    "You cannot use references to other worksheets or workbooks for Data Validation criteria."

    Thanks again to Moderator Rob for his answers here.

    As I mentioned in the other (now closed) thread, I had to add your formula in both the "Name Manager" (under "Formulas") *and* in the "Data Validation" (under "Data") menu options (but in "Data Validation", I had to remove any reference to the "Sheet" name, even if referencing the same sheet).

    Today, however, I tried to move the lookups to a different tab. It didn't work. Excel would let me reference a different tab ("worksheet") in the "Name Manager", but not in the "Data Validation" section.

    Am I resigned to hide the rows on the active tab?

    (I have Excel 2007, FWIW)


    Re: Dynamic named range, ignoring blanks

    Sorry about the code tags. Will remember in the future.

    Regarding the empty cells at the bottom, this is how I'm testing:

    1) I put "10/01/2010" in cell "L5" (and cells L6:L35 autofill with 10/02/2010 through 10/31/2010)
    2) I open the dropdown in cell A5 and it shows all 31 days
    3) I change the date in "L5" to 09/01/2010 (and cells L6:L35 autofill with 09/02/2010 through 09/30/2010)
    4) I left click on cell L36 (that says "10/01/2010") and press "Delete" on the keyboard
    5) I open the dropdown in cell A5 and it shows 30 days in September and a blank at the bottom of the list

    Does that help?

    I have Excel 2007. I have searched and found two threads that address this very problem:

    1) Ignore Blanks & Duplicates In Dynamic Named Range

    2) Stop Blanks In Drop Down List

    Yet when I attempt to implement the solutions, I do not see the expected results.

    I have a spreadsheet with a range of dates that changes every month. They are in cells L5:L35. In a 31-day month, all cells have data. But in a 30-, 29-, or 28-day month, those cells have empty cells at the bottom of the range.

    I have it defined as a named range "Dates".

    I do not want the blanks to show up in the dropdowns (which are in A5 - A50).

    In "Formulas", "Name Manager", I have "Dates" defined as:


    I also tried:


    but each time, the blank still shows up in the dropdown.

    FWIW, L5 has the date manually typed in on the first day of each month, then L6=L5+1, L7=L6+1, etc.

    What am I missing here? Thanks.