Macro cannot find shading colour black

[Aquinax]

I have a macro which should search & delete all paragraphs with black shading that works pretty well, except that it is unable to distinguish/find text with black shading colour. Here is the part of the macro that performs the search:

    Selection.Find.ClearFormatting
    With Selection.Find.ParagraphFormat
        .Shading.BackgroundPatternColor = wdColorBlack
    End With
    With Selection.Find
    .Wrap = wdFindContinue
    End With
    Selection.Find.Execute

If 'wdColorBlack' is substituted with yellow or red, the respective paragraphs are found by the macro, i.e. in the graphic below the two yellow and one red paragraphs are effortlessly found; if 'wdColorBlack' or '0' or '&H0' are specified, paragraphs with no shading are found by the macro along paragraphs in black shading.

VBAe.jpg

The following articles on "colours in Word 2007" explain that certain codes for theme or colour accent are added to the final colour code - wordarticles.com/Articles/Colours/2007BuildSet.php, wordarticles.com/Articles/Colours/2007.php - yet in the XML code of the document the text with black shading has no theme or colour accent modifiers. This is the corresponding excerpt from the .docx' XML code (the shading tag is marked up):

<w:p w:rsidR="00FE728A" w:rsidRPr="00FE728A" w:rsidRDefault="00FE728A" w:rsidP="00FE728A"><w:pPr><w:shd w:val="clear" w:color="auto" w:fill="000000"/><w:spacing w:after="0" w:line="570" w:lineRule="atLeast"/><w:rPr><w:rFonts w:ascii="scala-sans-sc-offc-pro--" w:eastAsia="Times New Roman" w:hAnsi="scala-sans-sc-offc-pro--" w:cs="Times New Roman"/><w:b/><w:bCs/><w:sz w:val="24"/><w:szCs w:val="24"/><w:lang w:val="en" w:eastAsia="en-GB"/></w:rPr></w:pPr><w:r w:rsidRPr="00FE728A"><w:rPr><w:rFonts w:ascii="scala-sans-sc-offc-pro--" w:eastAsia="Times New Roman" w:hAnsi="scala-sans-sc-offc-pro--" w:cs="Times New Roman"/><w:b/><w:bCs/><w:sz w:val="24"/><w:szCs w:val="24"/><w:lang w:val="en" w:eastAsia="en-GB"/></w:rPr><w:t xml:space="preserve">WORDS IN BLACK SHADING</w:t></w:r></w:p>

The XML code of the text without shading:

<w:p w:rsidR="000140C9" w:rsidRDefault="000140C9" w:rsidP="000140C9"><w:r><w:t xml:space="preserve">TEXT WITHOUT SHADING</w:t></w:r></w:p>

XML code of yellow shading:

<w:p w:rsidR="000140C9" w:rsidRDefault="000140C9" w:rsidP="000140C9"><w:pPr><w:shd w:val="clear" w:color="auto" w:fill="FF0000"/></w:pPr><w:r><w:t xml:space="preserve">TEXT IN YELLOW SHADING</w:t></w:r></w:p>

How should the search parameter .Shading.BackgroundPatternColor be specified so that it finds the text with black shading?

[macropod]

Cross-posted at: https://social.msdn.microsoft.com/Fo...?forum=worddev
Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq...._new_faq_item3

[Aquinax]

Can one search for the colour's HSL value? The macros below search for the Hex and RGB color values respectively:

With Selection.Find.ParagraphFormat
        .Shading.BackgroundPatternColor = &HFFFF&

        .Shading.BackgroundPatternColor = RGB (255,255,0)

Can HSL value be likewise specified? It can come in very handy when searching for a specific black colour HSL(x,0,0) where x can be any number 0-255, while RGB cannot differentiate here at all ... Thank you

[macropod]

Word works with RGB only. If you want to work with HSL, you'd need to use a function that converts HSL values to RGB. See, for example:

https://www.mrexcel.com/forum/excel-...9-hsl-rgb.html

Alternatively, you could work with CMYK, for which the conversion is quite simple:

Function RGBtoCMYK(R As Long, G As Long, B As Long) As String
Dim C As Single, M As Single, Y As Single, K As Single
C = 1 - (R / 255): M = 1 - (G / 255): Y = 1 - (B / 255): K = 1
If C < K Then K = C: If M < K Then K = M
If Y < K Then K = Y: If K = 1 Then
  C = 0: M = 0: Y = 0
Else
  C = (C - K) / (1 - K): M = (M - K) / (1 - K): Y = (Y - K) / (1 - K)
End If
RGBtoCMYK = "C: " & C & "M: " & M & "Y: " & Y & "K: " & K
End Function

The following macro shows how the function might be used:

Sub Test()
MsgBox RGBtoCMYK(255, 128, 0)
End Sub

[Aquinax]

The following macro does find and delete paragraphs with Shading colour Black

Dim par As Paragraph
    For Each par In ActiveDocument.Paragraphs
    If par.Shading.BackgroundPatternColor = -0 Then
    par.Range.DELETE
    End If
    Next par

but it seems to go through every paragraph and in large files, the deletion process often takes a lot of time making the file unresponsive. The macro below does find paragraphs with shading instantly and then deletes them quickly, however, it doesn't work with the colour black (-0, &H0&, RGB(0,0,0) etc) capturing paragraphs without shading as well.

Selection.Find.ClearFormatting
    With Selection.Find.ParagraphFormat
        .Shading.BackgroundPatternColor = &H0&
    End With
    With Selection.Find
        .Wrap = wdFindContinue
    End With
    
    Do While Selection.Find.Execute = True
        Selection.DELETE
    Loop

The specific colour of paragraphs that need to be deleted has the colour characteristics RGB(0,0,0), HSL(170,0,0).


Would it only be the colour issue, then the first macro wouldn't work either, yet it does - do I need to specify paragraphs as the search object as in the first macro, and how does one do that in a "With x.Find" construct? [an example with black shaded paragraphs is given in the attached file]

[Aquinax]

Basically, I need to find Paragraph Shading HSL (170, 0, 0). Can your macro do that?

The problem of finding paragraphs with a shading specified as HSL is only a workaround in the larger question of finding and deleting paragraphs with a specific colour shading, as specified here. If it can be solved, then I don't need a solution to the above question ...

[macropod]

The problem of finding paragraphs with a shading specified as HSL is only a workaround in the larger question of finding and deleting paragraphs with a specific colour shading, as specified here.

In which case, let's keep all the discussion in one thread. Merged.

[macropod]

Basically, I need to find Paragraph Shading HSL (170, 0, 0). Can your macro do that?

The code I posted is for RGB>CMYK conversions. It has nothing to do with 'finding' anything, as such - you'd need to incorporate it into a macro that applies the function as needed.

[Aquinax]

How does one use that:

' Color conversions revised 2009-11, 2015-0105
Public Declare Function ColorRGBToHLS Lib "shlwapi.dll" _
                                      (ByVal clrRGB As Long, _
                                       pwHue As Long, _
                                       pwLuminance As Long, _
                                       pwSaturation As Long) As Long

Public Declare Function ColorHLSToRGB Lib "shlwapi.dll" _
                                      (ByVal wHue As Long, _
                                       ByVal wLuminance As Long, _
                                       ByVal wSaturation As Long) As Long

Function RGBToHLS(iRed As Long, iGrn As Long, iBlu As Long) As Variant
  ' shg 2014
  ' wrapper for ColorRGBToHLS
  Dim iHue As Long
  Dim iLum As Long
  Dim iSat As Long
  
  ColorRGBToHLS RGB(iRed, iGrn, iBlu), iHue, iLum, iSat
  RGBToHLS = Array(iHue, iLum, iSat)
End Function

Function HLSToRGB(iHue As Long, iLum As Long, iSat As Long) As Variant
  ' shg 2014
  ' wrapper for ColorHLSToRGB
  Dim iRGB As Long
  
  iRGB = ColorHLSToRGB(iHue, iLum, iSat)
  HLSToRGB = Array(iRGB And 255, (iRGB \ 256) And 255, (iRGB \ 65536) And 255) End Function

Does one need to install 'shlwapi.dll' to use it? I'd need more specific directions on that advice, and macro. Perhaps, my second macro can be made to work by specifying the correct rabge/object since the first macro does work?

[macropod]

Read the information in the link. I'm not the author - I merely found the link for you.

Regardless, I doubt that approach is going to get you any closer to your goal...

[Aquinax]

The 'Public Declare' parts in the macro are marked immediately red in compiler. I meant more that I'd need to learn more about this macro, than you ought to explain it ... Besides it deals with HSL to RGB conversion, whereas I need to define HSL values (in order to search for them), and conversion to & definition in RGB terms would be of no use, as RGB is insensitive to 'hue' values of HSL which is crucial in finding the colours.

Regardless, I doubt that approach is going to get you any closer to your goal...

Why do you think so?
If the first of my macros does find the required black paragraphs, what is the reason the second fails? I have the feeling if I insert the 'par' or 'rg' correctly, it should do the find job just fine?...

[macropod]

Let me put it this way: If Word won't even return the RGB values, how do you suppose you're going to get something you can convert to HSL or CMYK?

[Aquinax]

I asked if Word can operate with HSL values so it can find specific shadings of paragraphs, conversion question is of no interest to me as such. What does interest me on the other hand is if the "For Each" method (working) can be remodeled into the "While Do" method (not working, but that is far more effective)

[macropod]

I asked if Word can operate with HSL values

And I answered that in post #4.

[Aquinax]

Of course,

what only interests me is whether the "While Do" method can be made working on the model of the (working) "For Each" method

[macropod]

what only interests me is whether the "While Do" method can be made working on the model of the (working) "For Each" method

There is no 'While Do' method - but the is a 'Do While ... Loop' loop structure and a 'While ... Wend' loop structure. Even so, if Word could locate the black shading via a Find (which it can't) and all you wanted to do is delete those paragraphs, you wouldn't need either loop structure.

[macropod]

In testing, what I've found is that a Find for:
.Shading.BackgroundPatternColorIndex = wdYellow
also Finds wdBlack. Evidently this is a bug. However, it can be worked around to explicitly identify wdBlack via code like:

Sub Demo()
Application.ScreenUpdating = False
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "*^13"
    .Replacement.Text = ""
    .ParagraphFormat.Shading.BackgroundPatternColorIndex = wdYellow
    .Format = True
    .Forward = True
    .MatchWildcards = True
    .Wrap = wdFindStop
    .Execute
  End With
  Do While .Find.Found
    If .Shading.BackgroundPatternColor = -587137025 Then .Delete
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
End With
Application.ScreenUpdating = True
End Sub

[Aquinax]

ShadingDialog.jpg
The identification "wdColorBlack", "wdBlack", &HDD000000, &H0, -0, 0, etc with BackgroundPatternColor is confused by Word with "No color" option and so all paragraphs with "No color" as Shading Fill are captured along black-shaded ones, and only black shaded paragraphs, other-coloured paragraphs are successfully distinguished and found by Word. If the search for Fill color black can be differentiated effectively from 'No color', the search for the black paragraphs will be successful.

[macropod]

You might at least try the code I posted...

[Aquinax]

I tried your macro certainly. The colour 'black' is found when searched for 'yellow' by you, because the search for black is too generic inside the Word's internal coding, is my strongest belief, and not because they (these two colours) specifically are confused by Word, thus by you all "Theme colours" (offered in the primary interface) are also found when searched for 'black' (and vice versa), because their colour code has a theme modifier prefix which also catches 'black' as it too has the same modifier and, I suspect, the internal coding for finding 'black' is too generic in Word. By me the search for the colours (non-black) results in successful finding them, while the search for 'black' results in finding all paragraphs without shading as well, along the black ones, while the coloured ones are jumped over. This is where the problem as well as the solution lies in my view - if one can tell Word to distinguish and skip the 'No colour' paragraphs, it will find the black ones only.