Date to be fixed

[khalil]

Hi all

please help

excel 2007

i want when fill cell B1 with any text , then cell A1= today , i want to fix this so when tomorrow come the date will not change

thanks in advance

 

[khalil]

thanks for the information

here is a question for you

i want to count number of cells in a row that has the same color via conditional formatting (excel 2007), normally without cods

thanks in advance

 

[MrKowz]

Counting cells based on their conditional format is a bit tricky. You can't directly tell excel to count based on the color, but we can tell excel to count how many times the condition is met.

That being said, what is the condition for your conditional format, and what range does it apply to?

 

[khalil]

Thanks
and you have a nice evening, we will stay in touch

 

[khalil]

good morning
can i use the code you gave in another sheet in the same workbook

thanks

 

[khalil]

New Thread excel 2007

Good evening my friend,

please help me in this one;

i have cells A1 B1 C1 contains formulas, they are not used yet,

when they are used and having data, I want just the formulas to jump

automatically to A2 B2 C2 cells,

and so on to jump to A3 B3 C3....... ( Just the formulas because the values will differ)

One other thing please; i want to create a user name for this macro enabled workbook so no body can copy it, and to hide the code so they cant disable
it.


many thanks
Khalil

 

[khalil]

Hello,
the code below works fine but if we can modify it little bit,

where when we remove the text the date will clear from the cell,

right now when you clear the text , the date stays...


Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Range("B:B")) Is Nothing Then
target.Offset(0, -1).Value = Date
End If
End Sub




thanks

 

[MrKowz]

Hello,
the code below works fine but if we can modify it little bit,

where when we remove the text the date will clear from the cell,

right now when you clear the text , the date stays...


Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Range("B:B")) Is Nothing Then
target.Offset(0, -1).Value = Date
End If
End Sub




thanks

Try:

Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Range("B:B")) Is Nothing Then
    If target.Value = "" Then
        target.Offset(0, -1).ClearContents
    Else
        target.Offset(0, -1).Value = Date
    End If
End If
End Sub

And to answer your other question. Yes, this can be used in other worksheets. Just copy/paste this code into each worksheet's module that you want it to run in.

And you mentioned:

i have cells A1 B1 C1 contains formulas, they are not used yet,

when they are used and having data, I want just the formulas to jump

automatically to A2 B2 C2 cells,

and so on to jump to A3 B3 C3....... ( Just the formulas because the values will differ)

One other thing please; i want to create a user name for this macro enabled workbook so no body can copy it, and to hide the code so they cant disable
it.


many thanks
Khalil

I'm not quite sure I understand what you mean. Can you please clarify?

 

[khalil]

thanks for help man,
the code works, but their is an issue here , when i save the codes and copy paste any row or copy past the work book , the codes will quit functioning,
then i must cut the code an paste it again and save, in order to work



about the other question you may ignore ,
any way what i meant that:
i am going to use on cell as an example,

suppose Cell A1 has a formula (array formula) connected to cell B1 & C1

when i fill B1 ,C1 ..... A1 will give a result, then what i wanted that formula in A1 Jump automatically to A2 and the fill cells will become B2 & C2,

i was thinking not to copy past the whole row down to the second row, because i have in one row too many formulas......

 

[MrKowz]

thanks for help man,
the code works, but their is an issue here , when i save the codes and copy paste any row or copy past the work book , the codes will quit functioning,
then i must cut the code an paste it again and save, in order to work

That is expected. The code is tied to the individual worksheet, not the cell(s).

What we COULD do is create a Workbook_Change event that will monitor every single worksheet in the workbook (that way you don't have to have a Worksheet_Change event for each worksheet). So that brings up a question, do you want this code to run on all worksheets in a given workbook, or do you want it to only run on specific ones? If on specific ones, what are the names of them?

 

[khalil]

Ok.

let us name the sheets

A & B & C

thanks