Posts by Haris1977

Thanks again for your quote!:)

Thanks! I think now is working..:)

Just a quick question: J9 cell will turn red on friday (25/10/19)?

1. Yes, you are right. I know Conditional Formatting is different than Macro's. I was using conditional formatting to enter your "formula" but i am getting error.
2. Smth = something (abbreviation)
3. Thanks again!

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)

Quote from Carim

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?

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)

[USER="31712"]Carim[/USER] hello again

I tried your code in J8 cell but i keep getting the same error.:(

I ve uploaded my xls. Can y implement your code for me? I want this to work not only for J8 cell but for the whole J column

Thanks!

[ATTACH]n1225047[/ATTACH]

I will test tomorrow on j8 cell and report back

Thanks!

This is what i did:( and this error occurs

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?

I get this error:

[ATTACH=JSON]{"data-align":"none","data-size":"large","data-attachmentid":1224999}[/ATTACH]

[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?

[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 will turn into GREEN
B) If there is a OXI in K8 cell, then the color of the WHOLE ROW (row 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

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

Thanks man! That works as expected!!

I know i have become a burden but there is an other issue that i may need your help (if y can help me with this - i would be very grateful :)).

As you can see in sheet1 in J column there are some dates inserted. Those dates are connected with A1 cell (which has the today date. That date in A1 is changing every day). Among with date numbers there are some N/A which is the abbreviation of Not Available data. There are 2 colors also. Red and green (and no color for N/A)

Lets say that the "test" cell is J6 (18/10/2019, which means October 18 of 2019).

I have created this condition: When J6 cell value=$A$1 (that condition applies to the whole J column of course), the specific cell turns into "RED". For cell value>$A$1 the cell becomes green.

Now what i also want is that:

For the red cells, i want this color to remain red UNTIL a "NAI" or "OXI" value is inserted into column K. Only until then the color will change to green (dont forget that with OXI the row is also been copied into sheet2 too of course). In other words if there is a NAI OR NO in K column, then the color of the adjacent cell will turn GREEN

I know that i am a pain in the ass, but is that possible?

Thanks i will wait.

Yes it is a unique number. But why do y ask? Will this help you with your code?

Well wait. Let me explain my self.

While your code works, it does it - partially.

Just make a test to see what i am talking about:

- Insert an OXI in Sheet's 1 K8 cell (in original.xlsx file which is my original file). It will insert this row as a line 19 in sheet2.
- Now import an OXI in Sheet's 1 K4 cell. It will insert this row as a line 20 in sheet2. Until now the code works as expected.

Now delete OXI in K8 cell. While the data of line 19 in sheet2 is gone, the data in line 20 now becomes the data in row 19. That is wrong. The row 19 should remain - but with no data in it.

[USER="31712"]Carim[/USER] hello again friend

It partially works. Make this test:

Put OXI in K4, K5 and K6 in that order. Row 19, 20 is created in sheet2. No line 21 though. I guess there is an error there

Oups: There is a whole line that is missing from your V2 file. I guess you delete it by accident. That made the code not work as excpected

This is the original file to check it again

ORIGINAL.xlsx

1. Correct.
2. When i delete OXI in Sheet1 cell K4, the range A;H which had been copied earlier to sheet2 row x IS NOT deleted (and i want to automatically been deleted). Can you add a code for that?