VBA code to copy entire row IF a specific word is in a SPECIFIC column

  • Oh..That seems to work ! Great thanks !:)


    Excellent ... !!!


    Thanks again for your Thanks ... AND for the Like ...:smile:


    P.S. Thanks for the Greek lesson ... where Yes is No ... and No is Yes ... :lol:

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

  • [USER="31712"]Carim[/USER] Thanks again for the file.


    As i see it works very well. Can y alter smth in your "code" that i recently noticed?


    As y see in 23/10/2019 (October, 23 of 2019) the J8 cell will turn into red. I want to stay red UNTIL there is a NAI or OXI in adjacent K column (in our example K8 cell).


    A) If there is a NAI in K8 cell, then the color of the WHOLE ROW (row 8) will turn into GREEN
    B) If there is a OXI in K8 cell, then the color of the WHOLE ROW (row 8) will turn into RED
    C) IF no NAI or OXI is entered into K8 cell, the J8 cell will remain red for ever !!!


    (I want this to happen for ALL J and K column as well)


    (PS NAI= yes and OXI= no in Greek language :))


    Can y alter it ?


    PS I did it my self, forget it :)

  • Congratulations on adjusting conditional formatting formulae ....:smile:


    P.S. In Greek ...NAI= Yes and OXI= No ... so Yes is No ... and No is Yes
    ...this must be very handy with Girls ...:lol:

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

  • [USER="31712"]Carim[/USER] thanks again for your help:


    Here is an other one that i am trying to accomplish: In J column there are some dates. In sheet1 A1 cell i have the TODAY() function.


    What i want to do is that: When the specific date comes (in my sheet lets say date in cell J8 - 23rd of October) i want this cell to become RED AND TO STAY RED UNTIL a NAI or OXI value is entered in column K.


    I have already achieved my first goal (cell becoming RED depending on A1 cell) by going to conditional Format=>highlight cell equal to A1 cell (which is the today cell) BUT i cant do the second one.


    Can y help?


    In other words how can i make the J column to STAY RED until A NAI/OXI value is entered in K column?

  • Just tested formula ... and it does produce the expected result ...


    Cannot replicate the error you are getting ...


    You should attach the file with your formula and the error ...

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

  • Wait. Maybe i am doing smth wrong.


    How do i add the formula?


    I just highlithed J column and went to conditional formating -> new rule -> use a formula to determine which cells to format (and copy pasted your formula)


    Am i doing smth wrong?

  • When Creating a conditional formatting rule ...


    you need to select very last option ... the Formula option...


    and Insert the proposed formula ...

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

  • You cannot select the whole J column ...


    First ... Test the formula on cell J8 ...


    Then ... When you are happy with the result ... afterwards, you can extend the range where formula Applies to ...

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

  • Thanks !


    But..J8, J9, J10, J11 and J13 cell are ALREADY IN RED COLOR. Why? These cells should be reds only in the specific date! (eg Cell J8 should be RED tomorrow, J9 in friday etc)


    ***In other words tomorrow J8 cell should be red (and only that cell) ! In Friday J9 cell should be red, in Saturday cell J10 etc..***


    (Remember that we write our dates different than the rest of world. E.g for 2nd of March 2019, we write 2/3/19 and not 3/2/19. I have the TODAY date in A1 cell)

  • Just remove the greater than sign ...


    Code
    =AND(ISNUMBER(J4),J4=$A$1,LEN(K4)=0)


    With:


    =AND(ISNUMBER(J4),J4=$A$1,LEN(K4)=0) i get error in code


    With:


    =AND(ISNUMBER(J4);J4=$A$1,LEN(K4)=0) no error but the cell does not become red (if i put e.g tomorrow's date in cell J14 as a test).


    Maybe the code is wrong?

  • Hmm..i see. I dunno why i keep getting these errors when i insert the macros. Maybe it is smth with my excel.


    I re-uploaded the file. Can you add this code to the file? Can you also check that it works as expected?


    (I recap for what i would want: i want J column cells - which have some upcoming dates - to become reds when those dates indicated on those cells will come (and stay red until a NAI/OXI in K column is inserted). E.g cell J8 should become red tomorrow, cell J9 in Friday etc.


    I ve become a real burden, sorry (that's the last i ask sorry) :P

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!