if part of text is in red color then true

[asterkhor]

hi,

I have an excel file which column A have thousands row of cells need to check if part of text is in RED color then show result as True else False.

I would like to have a macro to do it.

Sample:

ABC123,OK=123 True
123-dcvfg-a=123,456-tgb-b=234 False

I'm currently using formula to do it, but is fail when part of the text is not red.

my way is at below:
excel formula =txtColor(A1370)

Function txtColor(rng As Range)
txtColor = rng.Font.ColorIndex
End Function

[kersplash]

I don't know how you can check part of the cell for red text. Is there something that defines where the red text may appear within the cell? Delimited?

[asterkhor]

Hi,

It does not have common pattern.

[kersplash]

What is making it RED?

[jindon]

Use in cell like
=FindPartColor(A1,3)

Please Login or Register  to view this content.

[asterkhor]

hi,

It works perfectly! thanks a million!

[scottiex]

try

Please Login or Register  to view this content.

damn too slow again!

note you are using excel quite oddly to be having parts of cells partly coloured and then needing to know which cells are like that, you should try to avoid doing things like that if possible.

[jindon]

You are welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[asterkhor]

You are welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

I wrote a macro to execute these, however the result is #name?. Why?

[kersplash]

hi,

It works perfectly! thanks a million!

What happened?

[jindon]

I wrote a macro to execute these, however the result is #name?. Why?

How did you write it?
#name? appears when function name is wrongly entered in the cell.

[asterkhor]

hi,

I close the app and rerun macro, it works again.

but it's a bit weird, it appear #value? for 1 or 2 second, then it works again.

[jindon]

I close the app and rerun macro, it works again.

Without seeing how you wrote it, no one can even guess what is going on.

[asterkhor]

Without seeing how you wrote it, no one can even guess what is going on.

Thanks, I will attach the code here once back to office next week.

[asterkhor]

hi,

I'm back to office again.

here is the simple macro i wrote, however the result is #name?. Pls help to advice what to do to fix it?

Sub Macro1()
'

Dim LASTROW As Long

LASTROW = Range("A" & Rows.Count).End(xlUp).Row

Range("B5").Select
ActiveCell.FormulaR1C1 = _
"=(txtColor(RC[1],3))"

Range("B5").Select
Selection.AutoFill Destination:=Range("B5:B" & LASTROW)

Range("A5").Select

End Sub

Function txtColor(r As Range, clrIndex As Long) As Boolean
Dim i As Long
Application.Volatile
For i = 1 To Len(r.Value)
If r.Characters(i, 1).Font.ColorIndex = clrIndex Then
txtColor = True: Exit For
End If
Next
End Function

[jindon]

1)
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

2) You said

however the result is #name?.

No, I can not replicate the issue.

Upload a workbook.

[scottiex]

did you enter the function under the sheet in the vba and not under an ordinary module?
if so try moving the function to an ordinary module (eg module1)