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!

Sub ActiveFormatConditionThenRemoveContents()
    Dim cell As Range
    Dim lastRow As Long


    With ActiveSheet
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row


        For Each cell In .Range("AI101:AM" & lastRow)
            If (cell.DisplayFormat.Interior.Color = RGB(51, 51, 0) Or cell.DisplayFormat.Interior.Color = RGB(255, 255, 204)) And cell.value = "x" Then
               cell.value = ""
            End If
        Next cell
    End With


End Sub

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

It's now this...

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

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

Quote from Carim

Hello,

For the sake of simplicity ... both for you and for those who would like to help you out ...

Would encourage you to take advantage of the privilege offered by our Forum ...

Attach your sample file ... :wink:

Display More

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:

Sub ClearContents()
Dim cell As Range
For Each cell In Range("AI101:AM500")


If cell.Value = "x" And cell.Formula = "Long string of conditional formatting code here" Then
cell.Value = ""


ElseIf cell.Value = "y" And cell.Formula = "Long string of conditional formatting code here" Then
cell.Value = ""


End If
Next cell
End Sub

OR

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

Sub ClearContents()
Dim LastRow As Integer
Dim Row As Integer


LastRow = Range("AI101:AM" & Rows.Count).End(xlUp).Row


For Row = 101 To LastRow


If (cell.Value = "x" Or cell.Value = "y") And cell.Formula = "Long string of conditional formatting code here" Then
cell.Value = ""


End If
Next
End Sub

Thank you, thank you, thank you!!!

PERFECT!

I was just going to ask what the [] was after the _ParameterTable and I noticed my version of Excel (2010) removed them. It does EXACTLY what I needed!...No "0" when the corresponding $AI cell is blank!

Quote from arthurbr

Perhaps =IF(OR($AS$2="No",$AI2=""),"",ROUND($AI2*VLOOKUP($AJ$2,_ParameterTable[],19,0),0))

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

Quote from NBVC

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.

That still adds a zero when there's no data in the refering cell. Here's one of the sheets we pulled out. The column in question is in yellow highlight.

Notice the formula I use now doesn't add a zero when referring cell is blank but I'm trying to check a different cell ($AS$2) to kick in the calculation.

http://wikisend.com/download/981160/Book133.xls

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?

Quote from NBVC

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.

...if I change the last zero to "" for example.

=IF($AS$2="No","",ROUND($AI2*VLOOKUP($AJ$2,_ParameterTable,19,FALSE),0))

Works perfect if the initial IF is false; it displays a BLANK. But I can't edit the last zero so that an error or problem in the formula displays a BLANK as well.

Let me know if you can help.

Thanks!

Also posted here.

Got it. Thanks anyway.

asif = objHTA.parentwindow.clipboarddata.getdata("text")
asif1= Replace(asif,"HKCR", "HKEY_CLASSES_ROOT")
asif2= Replace(asif1,"HKCU", "HKEY_CURRENT_USER")
asif3= Replace(asif2,"HKLM", "HKEY_LOCAL_MACHINE")
cClipBoard= Replace(asif3, "HKU", "HKEY_USERS")
Set WshShell = WScript.CreateObject("WScript.Shell")

' Created by Sergey Tkachenko
' (c) http://winaero.com/
Dim objHTA
Dim cClipBoard
Dim WshShell
set objHTA=createobject("htmlfile")
cClipBoard=objHTA.parentwindow.clipboarddata.getdata("text")
Set WshShell = WScript.CreateObject("WScript.Shell")


[B]if findstring([cClipBoard], "HKCR")>=0 then
   Call Replace([cClipBoard], "HKCR", "HKEY_CLASSES_ROOT")
elseif findstring([cClipBoard], "HKCU")>=0 then
   Call Replace([cClipBoard], "HKCU", "HKEY_CURRENT_USER")
elseif findstring([cClipBoard], "HKLM")>=0 then
   Call Replace([cClipBoard], "HKLM", "HKEY_LOCAL_MACHINE")
elseif findstring([cClipBoard], "HKU")>=0 then
   Call Replace([cClipBoard], "HKU", "HKEY_USERS")
else
 [cClipBoard]
End If[/B]


WshShell.RegWrite "HKCU\Software\Microsoft\Windows\CurrentVersion\Applets\Regedit\LastKey", cClipBoard, "REG_SZ"
WshShell.Run "regedit.exe -m"
Set objHTA = nothing
Set WshShell = nothing
Set cClipBoard = nothing

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?

I forgot to mention that I added the .Wait delay in hopes of seeing what was happening. Sure enough, each page activated cell V2 and that was it.

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.

Dim Rng As Range
Dim c As Range
Dim Offset, cnt As Integer
Dim WSName As String
    Offset = 10
    For cnt = 1 + Offset To Range("_ParameterTable").Rows.Count + Offset
        If Sheets("Config").Range("J" & cnt).Value = "Yes" Then
            WSName = Sheets("Config").Range("A" & cnt).Value
            With Sheets(WSName)
                Worksheets(WSName).Activate


[COLOR=#FF0000][B]                   Set Rng = Range("$V2:$V30")
                    Range("V2:V30").Select
                        For Each c In Rng.Cells
                            c.Select
                            SendKeys "{F2}", True
                            SendKeys "{ENTER}", Wait:=True
                        Next


                    Range("V2").Select[/B][/COLOR]


            End With
        End If
[B]Application.Wait (Now + TimeValue("0:00:05"))[/B]
    Next

yes

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:

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

Example formulas (1):

=IF(ISBLANK('[B]G1-1[/B]'!$BB$1),"",'[B]G1-1[/B]'!$BB$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):

=IF(ISBLANK('[B]G2-2[/B]'!$BB$1),"",'[B]G2-2[/B]'!$BB$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"]

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

Row 11

G2-1

No

Row 12

G1-1

No

Row 13

G2-2

Yes

Row 14

G1-3

Yes

Row 15

G2-4

No

Row 16

G2-5

No

Row 17

G2-6

Yes

Row 18

G2-7

Yes

Row 19

G2-8

Yes

Row 20

G2-3

No

Row 21

G2-9

Yes

Row 22

G1-4

No

Row 23

G1-5

No

Row 24

G1-6

No

Row 25

G1-7

No

Row 26

G1-2

No

Row 27

G1-8

No

Row 28

G1-9

No

Row 29

G1-10

Yes

Row 30

G2-10

Yes

[/TABLE]

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

Finally, here's a basic sample file. It took forever to clean out and find all the hidden refs and links. I would have been much better just making it from scratch.

http://www.onlineautomation.com/download/Workbook2.xlsm

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.