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.

21953

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 (http://www.wordarticles.com/Articles/Colours/2007BuildSet.php), wordarticles.com/Articles/Colours/2007.php (http://www.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/Forums/office/en-US/3b001462-de22-4e72-a925-bb610cd91bab/macro-cannot-find-shading-colour-black?forum=worddev
Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#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-questions/861949-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 (http://www.vbaexpress.com/forum/showthread.php?62402-Macro-cannot-find-shading-colour-black&p=380036&viewfull=1#post380036). 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 (http://www.vbaexpress.com/forum/showthread.php?62402-Macro-cannot-find-shading-colour-black&p=380036&viewfull=1#post380036).
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]

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

[macropod]

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
Not so. There is a bug in the interaction with Find and BackgroundPatternColorIndex/BackgroundPatternColor. A Find for wdBlack Finds everything that doesn't have a Background Color. A Find for wdYellow Finds both Yellow and Black. The workaround is do a Find for wdYellow and to apply an additional test to determine which of Yellow and Black it is. That's what the code I posted does.

[Aquinax]

22246

By me your macro in its original form doesn't search at all. Only if I add Selection to With .Find and cut out the first lines like this:

Sub Demo()
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "*^13"
.Replacement.Text = ""
.ParagraphFormat.Shading.BackgroundPatternColorIndex = wdYellow
.Format = True
.Forward = True
.MatchWildcards = True
.Wrap = wdFindStop
.Execute
End With

End Sub
does it start to search and indeed it finds yellow and skips black; and also finds other colours. It is the "No colour" shading fill which is the problem for finding black shaded par-s.

[macropod]

By me your macro in its original form doesn't search at all. Only if I add Selection to With .Find and cut out the first lines like this:
The code I posted works fine; it's your poor attempt to implement a revision of it that's the problem...

[Aquinax]

Certainly, it works fine by you, at your machine and system with their own specifics. What is true is that it doesn't work by me and that "searching yellow finds black and it's a bug" is untrue, as searching yellow finds yellow and thus there's no bug, and what is also true is that searching black finds "No colour" as well, and this is the problem for me

[Kilroy]

This works for me:


Sub DeleteBlackShadedParagraphs()
Application.ScreenUpdating = False
With ActiveDocument.Range
With .Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "*^13"
.Replacement.Text = ""
.ParagraphFormat.Shading.BackgroundPatternColor = -587137025
.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

[Kilroy]

It doesn't seem to work on your sample document. I'm curious what method you're using to shade the paragraphs? When I choose shade "Black, Text 1" it works fine.

[Kilroy]

This works on your document:


Sub ChangeShadingAndThenDelete()
Application.ScreenUpdating = False
With ActiveDocument.Range
With .Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "*^13"
.Replacement.Text = ""
.ParagraphFormat.Shading.BackgroundPatternColor = RGB(0, 0, 0)
.Format = True
.Forward = True
.MatchWildcards = True
.Wrap = wdFindStop
.Execute
End With
Do While .Find.Found
If .Shading.BackgroundPatternColor = RGB(0, 0, 0) Then .Shading.BackgroundPatternColor = -587137025
.Collapse wdCollapseEnd
.Find.Execute
Loop
End With
With ActiveDocument.Range
With .Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "*^13"
.Replacement.Text = ""
.ParagraphFormat.Shading.BackgroundPatternColor = -587137025
.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

[Kilroy]

How did you apply the shading?