You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- VBA EXCEL SEARCH

Advertisement

QUESTION: Good day, I would like the possibility of a help of your knowledge.

I have a table with random values in A1:F23, I want to compare this table with another table in H2:M20 to find equal values and color these values equal red.

I'm using the below VBA but is giving error "If celula.Value = H2: M20 Then".

Would you like some help, or have any suggestions of a better VBA.

Sub SearchVal ()

'Declaring Variable

Dim celula As Object

'For Each cell between A1:F23 in sheet

For Each celula In Range ("A1:F23")

'If Cell Value is between H2: M20 Then

If celula.Value = H2:M20 Then

'Paint Red Cell (goes out of his Creativity)

celula.Interior.ColorIndex = 3

end If

Next

end Sub

Att Marcus,

ANSWER: Marcus,

Try

If Application.Countif(Range("H2:M20"),celula.Value) > 0 Then

rather than

If celula.Value = H2:M20 Then

But a better way is too use conditional formatting to test it and colour it, not VBA, and it automatically adjusts.

---------- FOLLOW-UP ----------

QUESTION: Ok Bob, thanks for listening, it was exactly what I wanted, however, he ended up highlighting everything randomly, I would compare in Table A1: E12 exact values sequential H5: H6 after L5: L6 after H7: L7 after H8 H9 after L8: L9 and then H10 L10? conditional formatting is not helping me. I'm sorry for english not very clear, I am not very good at English.

Sub SearchVal ()

'Declaring Variable

Dim celula As Object

'For Each cell between A1:F23 in sheet

For Each celula In Range ("A1:F23")

'If Cell Value is between H2: M20 Then

If Application.Countif(Range("H2:M20"),celula.Value) > 0 Then

'Paint Red Cell (goes out of his Creativity)

celula.Interior.ColorIndex = 3

end If

Next

end Sub

Att Marcus

Marcus,

I am not sure I understand what you are saying.

Did my suggestion work for you?

If it didn't, was it the VBA version that didn't work?

Did you try the conditional formatting approach?

Did that work?

What formula did you use?

Maybe try replying in English and also in your native language, I can use Google translate to try and work it out.

Bob

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | Ok, Bob, thank you for taking your attention and your time, solve the macro with your suggestions, thank you. |

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Can - VBA, Functions and formulas, PowerPivot, conditional formatting, data validation, charting

I have been using Excel for in excess of 20 yeras, and I am a 9 year Microsoft MVP**Organizations**

PASS
UK Developer Group
SQL Soton
SQL FAQ**Education/Credentials**

BSc Mathematics**Awards and Honors**

Microsoft MVP since 2005