+ Reply to Thread
Results 1 to 3 of 3

Conditional Formatting

  1. #1
    Registered User
    Join Date
    09-18-2009
    Location
    Auckland NZL
    MS-Off Ver
    Excel / Access / Word 2007
    Posts
    4

    Conditional Formatting

    I create report worksheets. While most data entry is into single cells there are some that are merged cells.
    I have been manually setting the cell conditional format to be say a yellow back colour if the cell is blank. When data is entered the cell reverts to its native colour.
    I can copy the (conditional) format from single cell to single cell, but it doesn't work to merged cells (breaks them up obviously).
    There is also different number formats among the single cell data entry cells and they end up inheriting all the format attributes from the copied cell.

    To save the repetitious conditional formatting is there a way I can script this in VBA. I could then assign this to say a button on the Quick Access Toolbar or a hot key.
    To say, for the selected cell in a worksheet conditionally format is so that when blank it has a yellow back colour.

    When I record a macro to do this I get:

    Sub CondFormatCell()
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=LEN(TRIM(W14))=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 65535
    .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True

    End Sub

    where W14 was the active cell.
    I understand the "=LEN(TRIM(W14))=0" means if the cell is blank, but I need this reference to be for any active cell or merged cells I choose on the worksheet and the conditional formatting to apply to the same cell(s). Hope that's clear.
    Any help appreciated

    Piri

  2. #2
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Conditional Formatting

    Please use the code tags.

    And if it is at all possible , post a dummy workbook to illustrate your problem.
    Be fore warned, I regularly post drunk. So don't take offence (too much) to what I say.
    I am the real 'Napster'
    The Grid. A digital frontier. I tried to picture clusters of information as they moved through the computer. What did they look like? Ships? motorcycles? Were the circuits like freeways? I kept dreaming of a world I thought I'd never see. And then, one day...

    If you receive help please give thanks. Click the * in the bottom left hand corner.

    snb's VBA Help Files

  3. #3
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Conditional Formatting

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1