Posts by Dougmeister

  • Test for existing background color in Conditional Formatting?

    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)

  • Parse cell for 1 of 3 values, return corresponding string value

    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"):


    Code
    =IF(FIND("GREEN",'(BC-1)'!A107)>0,"Green",IF(FIND("YELLOW",'(BC-1)'!A107)>0,"Yellow",IF(FIND("RED",'(BC-1)'!A107)>0,"Red")))


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


    Code
    =IFERROR(IF(FIND("GREEN",'(BC-1)'!A107)>0,"Green"),IFERROR(IF(FIND("YELLOW",'(BC-1)'!A107)>0,"Yellow"),IFERROR(IF(FIND("R",'(BC-1)'!A107)>0,"Red"),"***ERROR***")))


    Is there a more elegant solution? Thanks.

  • WEEKDAYNAME function not working... error #NAME? (Excel 2013)

    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]Syntax[/FONT]
    [FONT=&amp]WeekdayName( weekday , abbreviate , firstdayofweek )


    https://msdn.microsoft.com/en-…rary/office/gg278600.aspx[/FONT]
    [FONT=&amp]Yet when I type the following line into a cell, I get an error:
    [/FONT]
    [FONT=&amp]=WeekDayName(Now)[/FONT]
    [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?

  • Need faster code to strip rows where cell in column A of selection is blank

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


    Was using this:


    Code
    Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete


    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?


    Thanks.

  • Workbook open code doesn't execute after applying Excel 2007 self-cert on local PC

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


    If you mean:


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

  • Formatting percentage to 1 or 2 digits of precision depending on value...

    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:


    Code
    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:


    Code
    If (value <> 1) then
    wdRng.Text = Format(vValue, "Percent")
Else
    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.)


    Thanks.

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

    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":

    =OFFSET(Lookups!$L$2,0,0,COUNTA(Lookups!$L$2:$L$35),1)

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

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

    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)

    Thanks.

  • Dynamic named range, ignoring blanks

    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?

  • Dynamic named range, ignoring blanks

    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:

    =OFFSET(Data!$L$5,1,0,MATCH("*",Data!$L$5:$L$100,1),1)

    I also tried:

    =OFFSET(Data!$L$5,1,0,COUNTA(Data!$L$5:$L$35)-1,1)

    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.