Posts by wittonline

    DisplayFormat.Interior.Color was the KEY, thank you rlv01 from mrexcel!


    It's clunky and takes way longer than it should but it works. Peace!



    From a post in the other forum, I forgot to """" double quote everything INSIDE the main conditional formatting string!


    It's now this...

    Code
    1. "=IFERROR(INDEX('G2-7'!$BC:$BC,MATCH(""*""&$S101&""*"",'G2-7'!$BD:$BD,0),1),IF(INDEX('G2-7'!$BB:$BD,MATCH($H101,'G2-7'!$BB:$BB,0),3)="""",INDEX('G2-7'!$BB:$BD,MATCH($H101,'G2-7'!$BB:$BB,0),2),INDEX('G2-7'!$BB$1:$BP$20,MATCH($H101,'G2-7'!$BP:$BP,0),2)))<0"


    It's not huge because ALL my multiple conditional formatting for each area is saved on another sheet and brought over in place on demand, so I'm not worried about messing up, losing or breaking the code.


    I'm not trying to remove or add any conditional formatting code at all; so no need for FormatConditions.Delete or FormatConditions.Add code etc...


    All I need to do is just recognize, on demand (running a macro) when these 2 conditions are met then clear that cell's contents. (remove the x or y)


    Condition 1) Conditional Formatting above IS triggered. For what it's worth, when this conditional code is triggered (the conditions I'm looking for) the cell font is bold red and cell background Color = RGB(51, 51, 0) dark green/black.


    AND


    Condition 2) There's either an "x" or "y" VALUE.


    THEN


    ClearContents of THAT cell in the range.


    -----------------


    Maybe something like this...


    Code
    1. If (cell.Value = "x" Or cell.Value = "y") And R.DisplayFormat.Interior.Color = RGB(51, 51, 0) Then
    2. cell.Value = ""


    I wish it were that easy. This particular proprietary workbook has 30+ sheets, 100s of macros, and many sheets are interdependent of each other, including the conditional formatting code itself. I just can't put that online. I don't need someone to write the whole code, I just need help describing some parameters (i.e. DisplayFormat.Interior.Color) so the code can recognize whether the conditional formatting was triggered that we're looking for or not. Thanks.

    Note: Also here.



    I have extensive conditional formatting for an area on a (sheet) named "Data".


    I need to learn "how to", leave the conditional formatting alone, and on-demand...
    run a macro to ClearContents of ANY specific cells within the Range("AI101:AM" & lastRow)
    BUT ONLY when TWO conditions are met...


    1) Cell must contain an "x" or a "y" text value


    2) It also must satisfy the following conditional formatting FORMULA


    "=IFERROR(INDEX('G2-7'!$BC:$BC,MATCH("*"&$S101&"*",'G2-7'!$BD:$BD,0),1),IF(INDEX('G2-7'!$BB:$BD,MATCH($H101,'G2-7'!$BB:$BB,0),3)="",INDEX('G2-7'!$BB:$BD,MATCH($H101,'G2-7'!$BB:$BB,0),2),INDEX('G2-7'!$BB$1:$BP$20,MATCH($H101,'G2-7'!$BP:$BP,0),2)))<0"


    I might have been able to get this done if Excel would recognize conditional formatting within formulas and macros (i.e. If cell = whatever background color)


    Since it doesn't, one of the conditions must satisfy the conditional formatting formula that CAUSES the cell background color to change.



    Here's a couple macros I was working on.


    Main issue is that I doubt 'cell.Formula' is a way to describe satisfied Conditional Formatting



    Fixed Range:



    OR


    Variable Range: (I don't think Last Row Count code here is proper)


    I'm so sorry I wasn't more careful with the uploaded file!


    It was missing a sheet, therefore also missing _ParameterTable (named range)


    Cell AS2 isn't an issue. It will ALWAYS be either "Yes" or "No".


    My issue, as you can now see because I added the new formulas into the yellow highlight column on the G2-8 sheet, is that I want to use Cell AS2 as the KEY to decide whether to display values rather than using the W column same row (i.e. W2, W3, etc.) used in the old formulas.


    As you can see though, when the $AI column value is blank, instead of displaying nothing (blank) in column X, it displays "0". :(


    http://wikisend.com/download/193034/Book134.xlsm


    You don't show the "Config" sheet that your formula is referencing!


    If AS2 is resulting in #N/A! then there is no match found. The IFERROR() part of my formula should return a blank in that case.

    Thanks SO much for your patience!


    I got confused there. That's exactly right! It's when it uses the formula. It adds a "0" when there's no data in this cell $AI2, in this case.


    If $AS$2="No" is TRUE it works perfect. No "0" just a blank.


    I need to focus on the VLOOKUP portion: ROUND($AI2*VLOOKUP($AJ$2,_ParameterTable,19,FALSE)


    And when there's NO value in $AI2, have the result of the formula a BLANK.


    _ParameterTable,19 is always a number. It's a "1" when it doesn't add a weight %, and when I need the customer tables to be increased it's whatever factor I need.


    Does that make more sense?



    Sorry, I meant is AI2 a number? Check also column 19 of the ParameterTable to see if it is numeric and if there are any VALUE errors in there.


    Somehow this part $AI2*VLOOKUP($AJ$2,_ParameterTable,19,FALSE) is not resulting as a number that the ROUND function can use.


    Can you post a sample workbook showing the problem?

    Thank you for trying!


    This option adds a "0" when $AS$2="No" unfortunately. I can't use the formatting options either, because sometimes there's a legitimate zero in the cell I'm adding the formula. I just need to figure out how to make this work {$AS$2="No","",} and actually not add a zero. :)


    I don't THINK it matters but jic, $AJ$2 is not a number. It's a value similar to this...G2-1.



    Obviously I'm trying to add conditions that will handle the root key abbriviations for HKCR, HKCU, etc.


    Adding "Call" to the Replace function was the only way to get rid of the initial error "cannot use parentheses when calling a sub"


    Now I can't handle the "Type Mismatch" error on "findstring".


    The only thing I can think of is that it's not recognizing [cClipBoard]. I have no idea what object to use to describe and edit then.


    Maybe windows default clipboard contents cannot be edited and I have to edit it once written in LastKey registry?


    Note: If I use i.e. "HKCR\WIA.DeviceManager\CLSID" on the clipboard, the registry opens but not to any KEY, and checking WshShell.RegWrite "HKCU\Software\Microsoft\Windows\CurrentVersion\Applets\Regedit\LastKey" (Ironically the abbriviation is used here) THIS is the data in the LastKey = "HKCR\WIA.DeviceManager\CLSID", which of course needs to rather be "HKEY_CLASSES_ROOT\WIA.DeviceManager\CLSID"


    Any ideas?

    This may be a tough one. I cannot get a workable macro (in red) to work inside a function that activates multiple qualifying sheets one after the other, within the workbook.


    First, both macros work fine interdependently. I can replace the red portion with a whatever, say to write hi in cell A10, anything, and each "active" sheet that qualifies gets "hi" written in cell A10.


    And when I run the inner portion, in red, just on an Activesheet it too works fine! This is a very basic macro of course. It's just "hard" clicking in each cell in the range, that's it.


    But when I run them together, it's weird! It flys through each sheet, (too fast to possibly run the macro that does take a little time per sheet) and on the last sheet, I can see it working, going from one cell to the next, but only the last sheet. And oddly enough! Unlike when I run it once on one Activesheet it does NOT stop on cell V30. On the last sheet it keeps going on and on down the sheet! Like it's trying to make up for it's inability to run on every other sheet!


    All other sheets only have the first cell in the range (V2) as the active cell on each sheet. Like it prepared to run through the process but was unable.


    ANY help would really be appreciated.



    I'm a novice to formulas after working hard to get a small grasp on vba macros. I wanted to respond to see if I can help start the discussion with an idea because I've been dealing with similar formula issues as of late.


    Without more environment/logistics details, I believe this is not solvable as you're requesting. Embedding a variable path inside a formula needing the same variable path is somewhat of an unsolvable infinite loop. I assume that you have to embed the variables on an active sheet where the formula can access them initially.


    Then, assuming I have the single and double quotes in correct order, which is no guarantee, you might be able to use something like this:


    Code
    1. ='S:\Sales\Level 1\Daily Reporting\Daily Progress Report\["'"&'activesheet'!$B$3&"' Daily Report - '"&'activesheet'!$C$4&"'.xlsx"]011117'!$M$16

    Example formulas (1):


    Code
    1. =IF(ISBLANK('[B]G1-1[/B]'!$BB$1),"",'[B]G1-1[/B]'!$BB$1)


    Code
    1. =IF(ISBLANK([B]'G1-1[/B]'!$BP$1),"",IF('[B]G1-1[/B]'!$BC$1=-1,"",'[B]G1-1[/B]'!$BP$1))


    Example formulas (2):


    Code
    1. =IF(ISBLANK('[B]G2-2[/B]'!$BB$1),"",'[B]G2-2[/B]'!$BB$1)


    Code
    1. =IF(ISBLANK([B]'G2-2[/B]'!$BP$1),"",IF('[B]G2-2[/B]'!$BC$1=-1,"",'[B]G2-2[/B]'!$BP$1))



    The formulas, as you can see, just display cell values from other sheets, and remain blank if corresponding cell formulas have no value. I need to incorporate another variable and qualifying column, seen below.


    i.e. IF Column B (i.e. 'Config'!B:B) is YES of the corresponding value in Column A (i.e. 'Config'!A11:A30), (G1-1, G2-2, G1-3, etc.) then it's TRUE and I need to display the formula value above normally in each cell. IF Column B is NO of the corresponding value in Column A, then do NOT want to display formula value.



    Formulas (1):


    In this case the sheet code is (G1-1), which matches cell 'A12' and corresponding Column 'B12' = No, so NONE of the values from the formulas (1) above will display. All cells remain blank.


    Formulas (2):


    In this case the sheet code is (G2-2), which matches cell 'A13' and corresponding Column 'B13' = Yes, so ALL of the values from the formulas (2) above display normally.


    [TABLE="align: left, border: 1, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 64"]Sheet: 'Config'[/TD]
    [TD="width: 64"]Col A[/TD]
    [TD="width: 64"]Col B[/TD]

    [/tr]


    [tr]


    [td]

    Row 11

    [/td]


    [td]

    G2-1

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 12

    [/td]


    [td]

    G1-1

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 13

    [/td]


    [td]

    G2-2

    [/td]


    [td]

    Yes

    [/td]


    [/tr]


    [tr]


    [td]

    Row 14

    [/td]


    [td]

    G1-3

    [/td]


    [td]

    Yes

    [/td]


    [/tr]


    [tr]


    [td]

    Row 15

    [/td]


    [td]

    G2-4

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 16

    [/td]


    [td]

    G2-5

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 17

    [/td]


    [td]

    G2-6

    [/td]


    [td]

    Yes

    [/td]


    [/tr]


    [tr]


    [td]

    Row 18

    [/td]


    [td]

    G2-7

    [/td]


    [td]

    Yes

    [/td]


    [/tr]


    [tr]


    [td]

    Row 19

    [/td]


    [td]

    G2-8

    [/td]


    [td]

    Yes

    [/td]


    [/tr]


    [tr]


    [td]

    Row 20

    [/td]


    [td]

    G2-3

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 21

    [/td]


    [td]

    G2-9

    [/td]


    [td]

    Yes

    [/td]


    [/tr]


    [tr]


    [td]

    Row 22

    [/td]


    [td]

    G1-4

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 23

    [/td]


    [td]

    G1-5

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 24

    [/td]


    [td]

    G1-6

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 25

    [/td]


    [td]

    G1-7

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 26

    [/td]


    [td]

    G1-2

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 27

    [/td]


    [td]

    G1-8

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 28

    [/td]


    [td]

    G1-9

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 29

    [/td]


    [td]

    G1-10

    [/td]


    [td]

    Yes

    [/td]


    [/tr]


    [tr]


    [td]

    Row 30

    [/td]


    [td]

    G2-10

    [/td]


    [td]

    Yes

    [/td]


    [/tr]


    [/TABLE]

    Re: Conditional Formatting: Trouble adding a col qualifier value into an existing for


    From the reduced tables on the post above, here's a live example:


    Say the formula was in Col Z on 'Data' sheet and in a row with TX as State and 214 as Area Code in Col S as seen below. And since the corresponding row on sheet 'G2-2' Col BC=-1 from sheet 'G2-2' and the value '214' is also contained in sheet 'G2-2' within the range ('G2-2'!$BE$1:$BO$15) as seen below, SO the statement is TRUE and the formatting is applied.