IF cell is green add date
0
I am looking to have a cell M4 populate when I change either J4, K4 or L4 to a green fill.
For example: If I put the date 12-14 in K4 and change the background fill to Green, I want M4 to populate with the date 12-14.
microsoft-excel
edited Dec 14 '18 at 22:24
fixer1234
17.9k144681
asked Dec 14 '18 at 19:56
Mount IndieMount Indie
72
|
show 5 more comments
0
I am looking to have a cell M4 populate when I change either J4, K4 or L4 to a green fill.
For example: If I put the date 12-14 in K4 and change the background fill to Green, I want M4 to populate with the date 12-14.
microsoft-excel
edited Dec 14 '18 at 22:24
fixer1234
17.9k144681
asked Dec 14 '18 at 19:56
Mount IndieMount Indie
72
Hi @Mount Indie, welcome. I believe what you're looking for is called conditional formatting, have you looked into that? support.office.com/en-us/article/… You may want to retake the screenshot and include the row/column labels
– wysiwyg
Dec 14 '18 at 20:04
2
@wysiwyg Conditional formatting does the opposite of what Mount Indie wants to do.
– Worthwelle
Dec 14 '18 at 20:10
3
If you are manually changing the cell green to indicate something, and NOT changing it to green based on a value in the spreadsheet, Auto populating the contact date will require VBA. If there is something about the value of the cell of somewhere else in the sheet that cause the cell to be green than that can be done through conditional formatting, and the same criteria check used for conditional formatting can be used to auto populate your other cell.
– Forward Ed
Dec 14 '18 at 20:10
1
Formulas cannot detect colours of cells, but VBA can
– Forward Ed
Dec 14 '18 at 20:11
1
You could add a helper column beside each of your call dates. In this column you can add a letter to indicate the success of the call. You can then use condition formatting to color the date based on the value in the adjacent column. You can then also auto populate the the cell you want based on the value in the helper column being the condition check.
– Forward Ed
Dec 14 '18 at 20:38
|
show 5 more comments
0
0
0
I am looking to have a cell M4 populate when I change either J4, K4 or L4 to a green fill.
For example: If I put the date 12-14 in K4 and change the background fill to Green, I want M4 to populate with the date 12-14.
microsoft-excel
edited Dec 14 '18 at 22:24
fixer1234
17.9k144681
asked Dec 14 '18 at 19:56
Mount IndieMount Indie
72
I am looking to have a cell M4 populate when I change either J4, K4 or L4 to a green fill.
For example: If I put the date 12-14 in K4 and change the background fill to Green, I want M4 to populate with the date 12-14.
microsoft-excel
microsoft-excel
edited Dec 14 '18 at 22:24
fixer1234
17.9k144681
asked Dec 14 '18 at 19:56
Mount IndieMount Indie
72
edited Dec 14 '18 at 22:24
fixer1234
17.9k144681
asked Dec 14 '18 at 19:56
Mount IndieMount Indie
72
edited Dec 14 '18 at 22:24
fixer1234
17.9k144681
edited Dec 14 '18 at 22:24
fixer1234
17.9k144681
edited Dec 14 '18 at 22:24
fixer1234
17.9k144681
17.9k144681
asked Dec 14 '18 at 19:56
Mount IndieMount Indie
72
asked Dec 14 '18 at 19:56
Mount IndieMount Indie
72
asked Dec 14 '18 at 19:56
Mount IndieMount Indie
72
72
Hi @Mount Indie, welcome. I believe what you're looking for is called conditional formatting, have you looked into that? support.office.com/en-us/article/… You may want to retake the screenshot and include the row/column labels
– wysiwyg
Dec 14 '18 at 20:04
2
@wysiwyg Conditional formatting does the opposite of what Mount Indie wants to do.
– Worthwelle
Dec 14 '18 at 20:10
3
If you are manually changing the cell green to indicate something, and NOT changing it to green based on a value in the spreadsheet, Auto populating the contact date will require VBA. If there is something about the value of the cell of somewhere else in the sheet that cause the cell to be green than that can be done through conditional formatting, and the same criteria check used for conditional formatting can be used to auto populate your other cell.
– Forward Ed
Dec 14 '18 at 20:10
1
Formulas cannot detect colours of cells, but VBA can
– Forward Ed
Dec 14 '18 at 20:11
1
You could add a helper column beside each of your call dates. In this column you can add a letter to indicate the success of the call. You can then use condition formatting to color the date based on the value in the adjacent column. You can then also auto populate the the cell you want based on the value in the helper column being the condition check.
– Forward Ed
Dec 14 '18 at 20:38
|
show 5 more comments
Hi @Mount Indie, welcome. I believe what you're looking for is called conditional formatting, have you looked into that? support.office.com/en-us/article/… You may want to retake the screenshot and include the row/column labels
– wysiwyg
Dec 14 '18 at 20:04
2
@wysiwyg Conditional formatting does the opposite of what Mount Indie wants to do.
– Worthwelle
Dec 14 '18 at 20:10
3
If you are manually changing the cell green to indicate something, and NOT changing it to green based on a value in the spreadsheet, Auto populating the contact date will require VBA. If there is something about the value of the cell of somewhere else in the sheet that cause the cell to be green than that can be done through conditional formatting, and the same criteria check used for conditional formatting can be used to auto populate your other cell.
– Forward Ed
Dec 14 '18 at 20:10
1
Formulas cannot detect colours of cells, but VBA can
– Forward Ed
Dec 14 '18 at 20:11
1
You could add a helper column beside each of your call dates. In this column you can add a letter to indicate the success of the call. You can then use condition formatting to color the date based on the value in the adjacent column. You can then also auto populate the the cell you want based on the value in the helper column being the condition check.
– Forward Ed
Dec 14 '18 at 20:38
Hi @Mount Indie, welcome. I believe what you're looking for is called conditional formatting, have you looked into that? support.office.com/en-us/article/… You may want to retake the screenshot and include the row/column labels
– wysiwyg
Dec 14 '18 at 20:04
Hi @Mount Indie, welcome. I believe what you're looking for is called conditional formatting, have you looked into that? support.office.com/en-us/article/… You may want to retake the screenshot and include the row/column labels
– wysiwyg
Dec 14 '18 at 20:04
2
2
@wysiwyg Conditional formatting does the opposite of what Mount Indie wants to do.
– Worthwelle
Dec 14 '18 at 20:10
@wysiwyg Conditional formatting does the opposite of what Mount Indie wants to do.
– Worthwelle
Dec 14 '18 at 20:10
3
3
If you are manually changing the cell green to indicate something, and NOT changing it to green based on a value in the spreadsheet, Auto populating the contact date will require VBA. If there is something about the value of the cell of somewhere else in the sheet that cause the cell to be green than that can be done through conditional formatting, and the same criteria check used for conditional formatting can be used to auto populate your other cell.
– Forward Ed
Dec 14 '18 at 20:10
If you are manually changing the cell green to indicate something, and NOT changing it to green based on a value in the spreadsheet, Auto populating the contact date will require VBA. If there is something about the value of the cell of somewhere else in the sheet that cause the cell to be green than that can be done through conditional formatting, and the same criteria check used for conditional formatting can be used to auto populate your other cell.
– Forward Ed
Dec 14 '18 at 20:10
1
1
Formulas cannot detect colours of cells, but VBA can
– Forward Ed
Dec 14 '18 at 20:11
Formulas cannot detect colours of cells, but VBA can
– Forward Ed
Dec 14 '18 at 20:11
1
1
You could add a helper column beside each of your call dates. In this column you can add a letter to indicate the success of the call. You can then use condition formatting to color the date based on the value in the adjacent column. You can then also auto populate the the cell you want based on the value in the helper column being the condition check.
– Forward Ed
Dec 14 '18 at 20:38
You could add a helper column beside each of your call dates. In this column you can add a letter to indicate the success of the call. You can then use condition formatting to color the date based on the value in the adjacent column. You can then also auto populate the the cell you want based on the value in the helper column being the condition check.
– Forward Ed
Dec 14 '18 at 20:38
|
show 5 more comments
1 Answer
1
active
oldest
votes
0
To solve your problem, I would like to suggest a few macros.
Macro 1:
Private Sub Worksheet_Change(ByVal Target As Range)
Set w = ActiveSheet.Range("J4:L4")
For Each C In w
If C.Value <> "" And Not IsDate(C) Then
C.ClearContents
MsgBox "Only a Date is permitted in this cell."
End If
If C.Value = "" And Not IsDate(C) Then
C.Interior.ColorIndex = 0
Else
C.Interior.ColorIndex = 4
End If
Next C
End Sub
Macro 2:
Sub CopyLastCell()
Range("J4").End(xlToRight).Copy
Range("M4").PasteSpecial
Application.CutCopyMode = False
End Sub
How the Macro works:
- At the Active Sheet press
ALT+F11to openVBA Editor.
Copy & Paste both Macro as Standard Module.- The 1st Macro will only allow to enter
DATEin CellsJ4:L4and highlight cell/cells in Green Color, if findsDATEvalue.
RUN the Macro 2 when you want to Copy the Last Cell's value fromJ4:L4to CellM4.
N.B.
- I've used the LAST Cell Method to Copy to
M4because it's very much possible that you may enter DATE in all cells or in few.
- Data Range
J4:L4, destination CellM4&Color Indexvalues are editable.
edited Dec 16 '18 at 12:30
phuclv
8,97063889
answered Dec 16 '18 at 11:24
Rajesh SRajesh S
1
Glad to help you @Mount Indie keep asking ☺
– Rajesh S
Dec 18 '18 at 4:53
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1383674%2fif-cell-is-green-add-date%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
To solve your problem, I would like to suggest a few macros.
Macro 1:
Private Sub Worksheet_Change(ByVal Target As Range)
Set w = ActiveSheet.Range("J4:L4")
For Each C In w
If C.Value <> "" And Not IsDate(C) Then
C.ClearContents
MsgBox "Only a Date is permitted in this cell."
End If
If C.Value = "" And Not IsDate(C) Then
C.Interior.ColorIndex = 0
Else
C.Interior.ColorIndex = 4
End If
Next C
End Sub
Macro 2:
Sub CopyLastCell()
Range("J4").End(xlToRight).Copy
Range("M4").PasteSpecial
Application.CutCopyMode = False
End Sub
How the Macro works:
- At the Active Sheet press
ALT+F11to openVBA Editor.
Copy & Paste both Macro as Standard Module.- The 1st Macro will only allow to enter
DATEin CellsJ4:L4and highlight cell/cells in Green Color, if findsDATEvalue.
RUN the Macro 2 when you want to Copy the Last Cell's value fromJ4:L4to CellM4.
N.B.
- I've used the LAST Cell Method to Copy to
M4because it's very much possible that you may enter DATE in all cells or in few.
- Data Range
J4:L4, destination CellM4&Color Indexvalues are editable.
edited Dec 16 '18 at 12:30
phuclv
8,97063889
answered Dec 16 '18 at 11:24
Rajesh SRajesh S
1
Glad to help you @Mount Indie keep asking ☺
– Rajesh S
Dec 18 '18 at 4:53
add a comment |
0
To solve your problem, I would like to suggest a few macros.
Macro 1:
Private Sub Worksheet_Change(ByVal Target As Range)
Set w = ActiveSheet.Range("J4:L4")
For Each C In w
If C.Value <> "" And Not IsDate(C) Then
C.ClearContents
MsgBox "Only a Date is permitted in this cell."
End If
If C.Value = "" And Not IsDate(C) Then
C.Interior.ColorIndex = 0
Else
C.Interior.ColorIndex = 4
End If
Next C
End Sub
Macro 2:
Sub CopyLastCell()
Range("J4").End(xlToRight).Copy
Range("M4").PasteSpecial
Application.CutCopyMode = False
End Sub
How the Macro works:
- At the Active Sheet press
ALT+F11to openVBA Editor.
Copy & Paste both Macro as Standard Module.- The 1st Macro will only allow to enter
DATEin CellsJ4:L4and highlight cell/cells in Green Color, if findsDATEvalue.
RUN the Macro 2 when you want to Copy the Last Cell's value fromJ4:L4to CellM4.
N.B.
- I've used the LAST Cell Method to Copy to
M4because it's very much possible that you may enter DATE in all cells or in few.
- Data Range
J4:L4, destination CellM4&Color Indexvalues are editable.
edited Dec 16 '18 at 12:30
phuclv
8,97063889
answered Dec 16 '18 at 11:24
Rajesh SRajesh S
1
Glad to help you @Mount Indie keep asking ☺
– Rajesh S
Dec 18 '18 at 4:53
add a comment |
0
0
0
To solve your problem, I would like to suggest a few macros.
Macro 1:
Private Sub Worksheet_Change(ByVal Target As Range)
Set w = ActiveSheet.Range("J4:L4")
For Each C In w
If C.Value <> "" And Not IsDate(C) Then
C.ClearContents
MsgBox "Only a Date is permitted in this cell."
End If
If C.Value = "" And Not IsDate(C) Then
C.Interior.ColorIndex = 0
Else
C.Interior.ColorIndex = 4
End If
Next C
End Sub
Macro 2:
Sub CopyLastCell()
Range("J4").End(xlToRight).Copy
Range("M4").PasteSpecial
Application.CutCopyMode = False
End Sub
How the Macro works:
- At the Active Sheet press
ALT+F11to openVBA Editor.
Copy & Paste both Macro as Standard Module.- The 1st Macro will only allow to enter
DATEin CellsJ4:L4and highlight cell/cells in Green Color, if findsDATEvalue.
RUN the Macro 2 when you want to Copy the Last Cell's value fromJ4:L4to CellM4.
N.B.
- I've used the LAST Cell Method to Copy to
M4because it's very much possible that you may enter DATE in all cells or in few.
- Data Range
J4:L4, destination CellM4&Color Indexvalues are editable.
edited Dec 16 '18 at 12:30
phuclv
8,97063889
answered Dec 16 '18 at 11:24
Rajesh SRajesh S
1
To solve your problem, I would like to suggest a few macros.
Macro 1:
Private Sub Worksheet_Change(ByVal Target As Range)
Set w = ActiveSheet.Range("J4:L4")
For Each C In w
If C.Value <> "" And Not IsDate(C) Then
C.ClearContents
MsgBox "Only a Date is permitted in this cell."
End If
If C.Value = "" And Not IsDate(C) Then
C.Interior.ColorIndex = 0
Else
C.Interior.ColorIndex = 4
End If
Next C
End Sub
Macro 2:
Sub CopyLastCell()
Range("J4").End(xlToRight).Copy
Range("M4").PasteSpecial
Application.CutCopyMode = False
End Sub
How the Macro works:
- At the Active Sheet press
ALT+F11to openVBA Editor.
Copy & Paste both Macro as Standard Module.- The 1st Macro will only allow to enter
DATEin CellsJ4:L4and highlight cell/cells in Green Color, if findsDATEvalue.
RUN the Macro 2 when you want to Copy the Last Cell's value fromJ4:L4to CellM4.
N.B.
- I've used the LAST Cell Method to Copy to
M4because it's very much possible that you may enter DATE in all cells or in few.
- Data Range
J4:L4, destination CellM4&Color Indexvalues are editable.
edited Dec 16 '18 at 12:30
phuclv
8,97063889
answered Dec 16 '18 at 11:24
Rajesh SRajesh S
1
edited Dec 16 '18 at 12:30
phuclv
8,97063889
edited Dec 16 '18 at 12:30
phuclv
8,97063889
edited Dec 16 '18 at 12:30
phuclv
8,97063889
8,97063889
answered Dec 16 '18 at 11:24
Rajesh SRajesh S
1
answered Dec 16 '18 at 11:24
Rajesh SRajesh S
1
answered Dec 16 '18 at 11:24
Rajesh SRajesh S
1
1
Glad to help you @Mount Indie keep asking ☺
– Rajesh S
Dec 18 '18 at 4:53
add a comment |
Glad to help you @Mount Indie keep asking ☺
– Rajesh S
Dec 18 '18 at 4:53
Glad to help you @Mount Indie keep asking ☺
– Rajesh S
Dec 18 '18 at 4:53
Glad to help you @Mount Indie keep asking ☺
– Rajesh S
Dec 18 '18 at 4:53
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1383674%2fif-cell-is-green-add-date%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Hi @Mount Indie, welcome. I believe what you're looking for is called conditional formatting, have you looked into that? support.office.com/en-us/article/… You may want to retake the screenshot and include the row/column labels
– wysiwyg
Dec 14 '18 at 20:04
2
@wysiwyg Conditional formatting does the opposite of what Mount Indie wants to do.
– Worthwelle
Dec 14 '18 at 20:10
3
If you are manually changing the cell green to indicate something, and NOT changing it to green based on a value in the spreadsheet, Auto populating the contact date will require VBA. If there is something about the value of the cell of somewhere else in the sheet that cause the cell to be green than that can be done through conditional formatting, and the same criteria check used for conditional formatting can be used to auto populate your other cell.
– Forward Ed
Dec 14 '18 at 20:10
1
Formulas cannot detect colours of cells, but VBA can
– Forward Ed
Dec 14 '18 at 20:11
1
You could add a helper column beside each of your call dates. In this column you can add a letter to indicate the success of the call. You can then use condition formatting to color the date based on the value in the adjacent column. You can then also auto populate the the cell you want based on the value in the helper column being the condition check.
– Forward Ed
Dec 14 '18 at 20:38