When is black not blan

[reitsmad]

I have written code to highlight any word found in a cell from a list. eg. Look for the word "BUBBLE" in a search range and if found then change the color of "BUBBLE" to vbRed and put the number 1 in the next cell over. I then go through another list to check for false positives, eg. Look for "NO BUBBLE" in the same search range. If "NO BUBBLE" is found then change the color of "NO BUBBLE" to vbBlack.

After going through the false positives list I then run a sub routine call FalsePositives to check if the cell color in the search range is vbBlack because I don't want them displayed. Since in the above example "BUBBLE" which was vbRed has now been changed to vbBlack when "NO BUBBLE" was found I just go back through the cells and check to see if they are vbBlack now and then remove the 1's from the offset cells. When I physically look at the cell it is indeed vbBlack - RGB(0,0,0) but according to VB is it not so the offset values are not changed to "". I have tried to check the color of the cell by using font.color but shows blank rather than 0, which is what shows up for a cell that has not been changed. It seems to me that this is a bug in VB, but maybe my logic is just wrong. I am hoping someone can recommend a better way.

'HIGH search valuesFndRows = Range("A2", Range("A2").End(xlDown)).Rows.Count
Range("A2").Select
For x = 1 To FndRows  'Go through the list of values to search
    oStrg = ActiveCell.Value
    On Error Resume Next
    For Each cl In SrchRng
        If InStr(1, cl.Value, oStrg, vbTextCompare) > 0 Then
            cl.Offset(0, 1).Value = 1 'Mark it as found
            cl.Offset(0, 2).Value = 1 'Mark it as HIGH
        End If
        fndTxt = InStr(1, cl, oStrg, vbTextCompare)
        Do Until fndTxt = 0
            With cl.Characters(fndTxt, Len(oStrg))
                .Font.Color = vbRed 'RGB(255, 0, 0)
                .Font.Bold = True
            End With
            fndTxt = InStr(fndTxt + 1, cl, oStrg, vbTextCompare)
        Loop
    Next cl
    ActiveCell.Offset(1, 0).Select
Next


'Turn off highlighted search values that are false positives
FndRows = Range("E2", Range("E2").End(xlDown)).Rows.Count
Range("E2").Select
For x = 1 To FndRows  'Go through the list of values to search
    oStrg = ActiveCell.Value
    On Error Resume Next
    For Each cl In SrchRng
         If InStr(1, cl.Value, oStrg, vbTextCompare) > 0 Then 'Found a false positive
                cl.Offset(0, 6).Value = 1 'Mark false positive found
                fndTxt = InStr(1, cl, oStrg, vbTextCompare)
                Do Until fndTxt = 0
                    With cl.Characters(fndTxt, Len(oStrg))
                        .Font.Color = vbBlack
                        .Font.Bold = False
                    End With
                    fndTxt = InStr(fndTxt + 1, cl, oStrg, vbTextCompare)
                Loop
          End If
    Next cl
    ActiveCell.Offset(1, 0).Select
Next

Call FalsePositives



Sub FalsePositives()


ws.Target.Activate
' Turn off false positive cells - they only have black font color.
FndRows = Range("F5", Range("F5").End(xlDown)).Rows.Count
Range("F5").Select
For x = 1 To FndRows  'Go through the list of values to search
    If ActiveCell.Offset(0, 6).Value = 1 Then 'Highlighted as having a false positive
        If ActiveCell.Font.Color = vbBlack Then
            'ActiveCell.Offset(0, 1).Value = ""
            'ActiveCell.Offset(0, 2).Value = ""
        End If
    End If
    ActiveCell.Offset(1, 0).Select
Next




End Sub

 

[Fluff]

Misread code.

 

[Fluff]

First off, what is SrchRng there is nothing in the code assigning it a value?

 

[reitsmad]

Re: When is black not black

First off, what is SrchRng there is nothing in the code assigning it a value?

SrchRng is in the target worksheet where I have imported data....
wsTarget.Activate


Set SrchRng = Range(Range("F5"), Range("F" & Rows.Count).End(xlUp))

I then switch back to the worksheet that has the search criteria. That all works okay. Just the part where I check to see if the cell is all black so I can change removed the 1's in the offsets doesn't work because VB doesn't read it as being black.

 

[Fluff]

Re: When is black not black

should this

ws.Target.Activate

have that first period?

 

[reitsmad]

Re: When is black not black

It is correct. Only problem is the FalsePositive sub routine. It should be black but for some reason VB thinks the changed cell color is something else.

 

[MARK858]

Re: When is black not black

Really? so where is Target defined as a variable for a range? or even where is ws defined?

 

[reitsmad]

Re: When is black not black

Really? so where is Target defined as a variable for a range? or even where is ws defined?

What's your point? Looks like I provided too much information and perhaps not understanding the problem I was inquiring about?

It appears (literally) to work well enough changing red to black in the code if false positive text in...

With cl.Characters(fndTxt, Len(oStrg))
.Font.Color = RGB(0, 0, 0)
End With

however as I have not found out the color is actually changed to decimal 16777216. Since the rest of the text in the cell that is unaltered is true black - vbBLACK, RGB(0,0,0) or colorindex = 1 which is decimal 000000.

So of course when I checked to see if the characters in the cell are all black

If ActiveCell.Font.Color = RGB(0,0,0) Then

...it didn't work because there are actually still two difference character colors in the cell (decimal 0 and decimal 16777216).

I have solved the problem though!!!

With cl.Characters(fndTxt, Len(oStrg))
.Font.Color = 0
End With

and in the FalsePositive subroutine...

If ActiveCell.Font.Color = 0 Then


I have no idea why its didn't work with vbBLACK or RBG(0,0,0) or ColorIndex = 1 for that matter because they are black but I'm fine with the solution I have found.

 

[reitsmad]

Re: When is black not black

Hmmm, maybe not. Looks like it worked once.

 

[MARK858]

Re: When is black not black

What's your point? Looks like I provided too much information and perhaps not understanding the problem I was inquiring about?

The point was that I was trying to work out if you were evaluating the correct cell, in other words making sure that the correct sheet had been activated.

So no you didn't provide too much information, rather too little.

On a different track, if you select the cell what does the code below produce.

Sub RGB_Font()
    Dim HxCol As String, RGBcol As String

    HxCol = Right("000000" & Hex(ActiveCell.Font.Color), 6)

    RGBcol = "RGB (" & CInt("&H" & Right(HxCol, 2)) & _
             ", " & CInt("&H" & Mid(HxCol, 3, 2)) & _
             ", " & CInt("&H" & Left(HxCol, 2)) & ")"

    MsgBox RGBcol, vbInformation, "Cell " & ActiveCell.Address(0, 0) & ":  Fill Color"

End Sub