VBA row highlight if specific cell not empty

  • Hi! I would like ask, how possible do in VBA highlight row if specific cell not empty.
    For example i have numbers in "A" column 1,2,3,4,5 and if have value next to 1,3 and 5 in "B" column like: 1 | a , 3 | b, 5 | c highlight this 3 row from "A" to "C".

  • Hello,


    Much better than a description which is always difficult to clearly explain ...


    Why don't you attach a sample file ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Yes you right about it, so i have 2 files. The first name always fix, but the second file always change, i wouldnt like change in code each time when i want run the macro.

    In 1.xlsx file have lot of lines, if i have same line in 2.xlsx i would like highlight,

    Files

    • 1.xlsx

      (8.84 kB, downloaded 39 times, last: )
    • 2.xlsx

      (9.15 kB, downloaded 37 times, last: )
  • Thank you very much your help! I have just one problem. Later i have to use the 2.xlx file to run script in other app, thats why sadly i can not change the format to .xlsm from .xlsx. Possible to do it from 1.xlsx? Its really big help for me!

    Sorry i forget said to you 2 more things. Can be couple of thousand line, and in 2.xlsx file can be lines with value in "C" whoch different than 1.xlsx.

  • Re,


    Not sure what you mean by ' to do it from 1.xlsx ' ...


    Do you already have macros in 1.xlsx ... which should be 1.xlsm ... ?


    Is your file 2.xlsx generated by 1.xlsx ...?


    Are you working with opened or closed files ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • In 1.xlsx i have a massive list which is couple of thousand lines. In 2.xlsx file i have like 5-10 or sometimes 30 lines. So i put the value from 1.xlsx to 2.xlsx, after than i would like highlight the row where is the new value.

    Yes i have macro in 1.xlsx, which automatic put the value from 1.xlsx to 2.xlsx, but i would like highlight as well. So yes the 1. file form is 1.xlsm, but i can put other button wich highlight button and its can highlight in 2.xlsx file row.

    2.xlsx file we create this, we put same number from email, and after we have search in 1.xlsx file the value for number, but this part done with macro, i would like just highlight this rows.

    I work with opened files. I hope i explain to you everything. Thank you your help!

  • Quote

    Yes i have macro in 1.xlsx, which automatic put the value from 1.xlsx to 2.xlsx


    Just two questions :


    1. Why not modifying the macro you already have to Add the Color ...?


    2. Why aren't you posting the macro you have ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • I did try change the macro but the highlight not work very well, because this code highlight the empty cell as well. Please see below my code:


  • Re,


    Your explanations are very confusing ...


    You could test following instructions to replace your lines 34 to 54


    Code
    1. For Each ocell In ws2.Range("A2:A" & i)
    2. For Each Key In Dic
    3. If ocell.Value = Key Then
    4. ocell.Offset(, 3).Value = Dic(Key)
    5. If Not IsEmpty(ocell) Then
    6. Range(Cells(ocell.Row, 1), Cells(ocell.Row, 7)).Interior.ColorIndex = 37
    7. End If
    8. End If
    9. Next Key
    10. Next ocell

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re,


    To be tested from workbook 1 ...


    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re,


    Very surprising ...!!!


    Make sure what you are calling ' empty cells ' ... ARE indeed EMPTY cells ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • I should integrate this to my code, but the problem is for me thsi code not work, and you have fix file name for 2.xlsx.

    I try run run jsut this code but nothing happen. I dont know where is the problem because i didnt got any errors.