Is it possible to have excel circle the cell that contains today's date?
I have a big calendar spreadsheet that currently just highlights today's date with conditional formatting. However, I do not like any of the formats I could possibly apply and so I would love to be able to have something whereby a circle is drawn over the top of the cell with today's date in.
The way each day is laid out is so that (for example) A1 is the date itself in "dd" format and then A2 has the information in. The information is pulled through from an event data list so it has a formula in.
I have seen some stuff on this being possible with a VBA code but I am just not sure how to write that.
Thank you
Sam
excel vba excel-vba excel-formula
edited Nov 14 '18 at 12:50
Pᴇʜ
22.2k42750
asked Nov 14 '18 at 12:48
Sam CollinsSam Collins
41
add a comment |
I have a big calendar spreadsheet that currently just highlights today's date with conditional formatting. However, I do not like any of the formats I could possibly apply and so I would love to be able to have something whereby a circle is drawn over the top of the cell with today's date in.
The way each day is laid out is so that (for example) A1 is the date itself in "dd" format and then A2 has the information in. The information is pulled through from an event data list so it has a formula in.
I have seen some stuff on this being possible with a VBA code but I am just not sure how to write that.
Thank you
Sam
excel vba excel-vba excel-formula
edited Nov 14 '18 at 12:50
Pᴇʜ
22.2k42750
asked Nov 14 '18 at 12:48
Sam CollinsSam Collins
41
1
I bet you could have a shape anchored at the corner of the cell with today’s date. Wish I could take a crack at this one
– Marcucciboy2
Nov 14 '18 at 12:50
Does it have to be a circle, or could you use .Borders.LineStyle = xlContinuous?
– Cyril
Nov 14 '18 at 12:53
add a comment |
I have a big calendar spreadsheet that currently just highlights today's date with conditional formatting. However, I do not like any of the formats I could possibly apply and so I would love to be able to have something whereby a circle is drawn over the top of the cell with today's date in.
The way each day is laid out is so that (for example) A1 is the date itself in "dd" format and then A2 has the information in. The information is pulled through from an event data list so it has a formula in.
I have seen some stuff on this being possible with a VBA code but I am just not sure how to write that.
Thank you
Sam
excel vba excel-vba excel-formula
edited Nov 14 '18 at 12:50
Pᴇʜ
22.2k42750
asked Nov 14 '18 at 12:48
Sam CollinsSam Collins
41
I have a big calendar spreadsheet that currently just highlights today's date with conditional formatting. However, I do not like any of the formats I could possibly apply and so I would love to be able to have something whereby a circle is drawn over the top of the cell with today's date in.
The way each day is laid out is so that (for example) A1 is the date itself in "dd" format and then A2 has the information in. The information is pulled through from an event data list so it has a formula in.
I have seen some stuff on this being possible with a VBA code but I am just not sure how to write that.
Thank you
Sam
excel vba excel-vba excel-formula
excel vba excel-vba excel-formula
edited Nov 14 '18 at 12:50
Pᴇʜ
22.2k42750
asked Nov 14 '18 at 12:48
Sam CollinsSam Collins
41
edited Nov 14 '18 at 12:50
Pᴇʜ
22.2k42750
asked Nov 14 '18 at 12:48
Sam CollinsSam Collins
41
edited Nov 14 '18 at 12:50
Pᴇʜ
22.2k42750
edited Nov 14 '18 at 12:50
Pᴇʜ
22.2k42750
edited Nov 14 '18 at 12:50
Pᴇʜ
22.2k42750
22.2k42750
asked Nov 14 '18 at 12:48
Sam CollinsSam Collins
41
asked Nov 14 '18 at 12:48
Sam CollinsSam Collins
41
asked Nov 14 '18 at 12:48
Sam CollinsSam Collins
41
41
1
I bet you could have a shape anchored at the corner of the cell with today’s date. Wish I could take a crack at this one
– Marcucciboy2
Nov 14 '18 at 12:50
Does it have to be a circle, or could you use .Borders.LineStyle = xlContinuous?
– Cyril
Nov 14 '18 at 12:53
add a comment |
1
I bet you could have a shape anchored at the corner of the cell with today’s date. Wish I could take a crack at this one
– Marcucciboy2
Nov 14 '18 at 12:50
Does it have to be a circle, or could you use .Borders.LineStyle = xlContinuous?
– Cyril
Nov 14 '18 at 12:53
1
1
I bet you could have a shape anchored at the corner of the cell with today’s date. Wish I could take a crack at this one
– Marcucciboy2
Nov 14 '18 at 12:50
I bet you could have a shape anchored at the corner of the cell with today’s date. Wish I could take a crack at this one
– Marcucciboy2
Nov 14 '18 at 12:50
Does it have to be a circle, or could you use .Borders.LineStyle = xlContinuous?
– Cyril
Nov 14 '18 at 12:53
Does it have to be a circle, or could you use .Borders.LineStyle = xlContinuous?
– Cyril
Nov 14 '18 at 12:53
add a comment |
1 Answer
1
active
oldest
votes
This will draw an oval round today's date. If today's date is not found, an error message will be shown:
Sub DrawOval()
Dim cell As Range, circ As Shape
Set cell = Sheet1.Cells.Find(Date, Sheet1.Range("A1"))
If Not cell Is Nothing Then
Set circ = Sheet1.Shapes.AddShape(msoShapeOval, 187.8, 37.2, 63.6, 24)
With circ
.Select
Selection.ShapeRange.Fill.Visible = msoFalse
.Top = cell.Top
.Left = cell.Left
End With
Else
MsgBox "Cell with today's date not found!", vbCritical + vbOKOnly, "Error"
End If
End Sub
This assumes your worksheet name is Sheet1, so amend that accordingly. You can run this by adding a shape to your worksheet, and assigning this macro to it.
edited Nov 14 '18 at 14:50
Marcucciboy2
2,38521022
answered Nov 14 '18 at 13:07
NickNick
79711231
2
Just a thought (not looked at improving the maths on it) - change the Top, Left, Height and Width to:.Top = cell.Top - (cell.Offset(-1).Height / 2),.Left = cell.Left - (cell.Offset(, -1).Width / 4),.Height = cell.Height + (cell.Offset(-1).Height / 2) + (cell.Offset(1).Height / 2),.Width = cell.Width + (cell.Offset(, -1).Width / 4) + (cell.Offset(, 1).Width / 4)so the circle extends into the neighbouring cells.
– Darren Bartrup-Cook
Nov 14 '18 at 13:33
I believe you can avoid theselectpart of your code by just trimming down toWith circ .ShapeRange. ...
– Marcucciboy2
Nov 14 '18 at 14:49
@Marcucciboy2 no, that will throw a not supported error.
– Nick
Nov 14 '18 at 15:04
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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
});
}
});
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%2fstackoverflow.com%2fquestions%2f53300628%2fis-it-possible-to-have-excel-circle-the-cell-that-contains-todays-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
This will draw an oval round today's date. If today's date is not found, an error message will be shown:
Sub DrawOval()
Dim cell As Range, circ As Shape
Set cell = Sheet1.Cells.Find(Date, Sheet1.Range("A1"))
If Not cell Is Nothing Then
Set circ = Sheet1.Shapes.AddShape(msoShapeOval, 187.8, 37.2, 63.6, 24)
With circ
.Select
Selection.ShapeRange.Fill.Visible = msoFalse
.Top = cell.Top
.Left = cell.Left
End With
Else
MsgBox "Cell with today's date not found!", vbCritical + vbOKOnly, "Error"
End If
End Sub
This assumes your worksheet name is Sheet1, so amend that accordingly. You can run this by adding a shape to your worksheet, and assigning this macro to it.
edited Nov 14 '18 at 14:50
Marcucciboy2
2,38521022
answered Nov 14 '18 at 13:07
NickNick
79711231
2
Just a thought (not looked at improving the maths on it) - change the Top, Left, Height and Width to:.Top = cell.Top - (cell.Offset(-1).Height / 2),.Left = cell.Left - (cell.Offset(, -1).Width / 4),.Height = cell.Height + (cell.Offset(-1).Height / 2) + (cell.Offset(1).Height / 2),.Width = cell.Width + (cell.Offset(, -1).Width / 4) + (cell.Offset(, 1).Width / 4)so the circle extends into the neighbouring cells.
– Darren Bartrup-Cook
Nov 14 '18 at 13:33
I believe you can avoid theselectpart of your code by just trimming down toWith circ .ShapeRange. ...
– Marcucciboy2
Nov 14 '18 at 14:49
@Marcucciboy2 no, that will throw a not supported error.
– Nick
Nov 14 '18 at 15:04
add a comment |
This will draw an oval round today's date. If today's date is not found, an error message will be shown:
Sub DrawOval()
Dim cell As Range, circ As Shape
Set cell = Sheet1.Cells.Find(Date, Sheet1.Range("A1"))
If Not cell Is Nothing Then
Set circ = Sheet1.Shapes.AddShape(msoShapeOval, 187.8, 37.2, 63.6, 24)
With circ
.Select
Selection.ShapeRange.Fill.Visible = msoFalse
.Top = cell.Top
.Left = cell.Left
End With
Else
MsgBox "Cell with today's date not found!", vbCritical + vbOKOnly, "Error"
End If
End Sub
This assumes your worksheet name is Sheet1, so amend that accordingly. You can run this by adding a shape to your worksheet, and assigning this macro to it.
edited Nov 14 '18 at 14:50
Marcucciboy2
2,38521022
answered Nov 14 '18 at 13:07
NickNick
79711231
2
Just a thought (not looked at improving the maths on it) - change the Top, Left, Height and Width to:.Top = cell.Top - (cell.Offset(-1).Height / 2),.Left = cell.Left - (cell.Offset(, -1).Width / 4),.Height = cell.Height + (cell.Offset(-1).Height / 2) + (cell.Offset(1).Height / 2),.Width = cell.Width + (cell.Offset(, -1).Width / 4) + (cell.Offset(, 1).Width / 4)so the circle extends into the neighbouring cells.
– Darren Bartrup-Cook
Nov 14 '18 at 13:33
I believe you can avoid theselectpart of your code by just trimming down toWith circ .ShapeRange. ...
– Marcucciboy2
Nov 14 '18 at 14:49
@Marcucciboy2 no, that will throw a not supported error.
– Nick
Nov 14 '18 at 15:04
add a comment |
This will draw an oval round today's date. If today's date is not found, an error message will be shown:
Sub DrawOval()
Dim cell As Range, circ As Shape
Set cell = Sheet1.Cells.Find(Date, Sheet1.Range("A1"))
If Not cell Is Nothing Then
Set circ = Sheet1.Shapes.AddShape(msoShapeOval, 187.8, 37.2, 63.6, 24)
With circ
.Select
Selection.ShapeRange.Fill.Visible = msoFalse
.Top = cell.Top
.Left = cell.Left
End With
Else
MsgBox "Cell with today's date not found!", vbCritical + vbOKOnly, "Error"
End If
End Sub
This assumes your worksheet name is Sheet1, so amend that accordingly. You can run this by adding a shape to your worksheet, and assigning this macro to it.
edited Nov 14 '18 at 14:50
Marcucciboy2
2,38521022
answered Nov 14 '18 at 13:07
NickNick
79711231
This will draw an oval round today's date. If today's date is not found, an error message will be shown:
Sub DrawOval()
Dim cell As Range, circ As Shape
Set cell = Sheet1.Cells.Find(Date, Sheet1.Range("A1"))
If Not cell Is Nothing Then
Set circ = Sheet1.Shapes.AddShape(msoShapeOval, 187.8, 37.2, 63.6, 24)
With circ
.Select
Selection.ShapeRange.Fill.Visible = msoFalse
.Top = cell.Top
.Left = cell.Left
End With
Else
MsgBox "Cell with today's date not found!", vbCritical + vbOKOnly, "Error"
End If
End Sub
This assumes your worksheet name is Sheet1, so amend that accordingly. You can run this by adding a shape to your worksheet, and assigning this macro to it.
edited Nov 14 '18 at 14:50
Marcucciboy2
2,38521022
answered Nov 14 '18 at 13:07
NickNick
79711231
edited Nov 14 '18 at 14:50
Marcucciboy2
2,38521022
edited Nov 14 '18 at 14:50
Marcucciboy2
2,38521022
edited Nov 14 '18 at 14:50
Marcucciboy2
2,38521022
2,38521022
answered Nov 14 '18 at 13:07
NickNick
79711231
answered Nov 14 '18 at 13:07
NickNick
79711231
answered Nov 14 '18 at 13:07
NickNick
79711231
79711231
2
Just a thought (not looked at improving the maths on it) - change the Top, Left, Height and Width to:.Top = cell.Top - (cell.Offset(-1).Height / 2),.Left = cell.Left - (cell.Offset(, -1).Width / 4),.Height = cell.Height + (cell.Offset(-1).Height / 2) + (cell.Offset(1).Height / 2),.Width = cell.Width + (cell.Offset(, -1).Width / 4) + (cell.Offset(, 1).Width / 4)so the circle extends into the neighbouring cells.
– Darren Bartrup-Cook
Nov 14 '18 at 13:33
I believe you can avoid theselectpart of your code by just trimming down toWith circ .ShapeRange. ...
– Marcucciboy2
Nov 14 '18 at 14:49
@Marcucciboy2 no, that will throw a not supported error.
– Nick
Nov 14 '18 at 15:04
add a comment |
2
Just a thought (not looked at improving the maths on it) - change the Top, Left, Height and Width to:.Top = cell.Top - (cell.Offset(-1).Height / 2),.Left = cell.Left - (cell.Offset(, -1).Width / 4),.Height = cell.Height + (cell.Offset(-1).Height / 2) + (cell.Offset(1).Height / 2),.Width = cell.Width + (cell.Offset(, -1).Width / 4) + (cell.Offset(, 1).Width / 4)so the circle extends into the neighbouring cells.
– Darren Bartrup-Cook
Nov 14 '18 at 13:33
I believe you can avoid theselectpart of your code by just trimming down toWith circ .ShapeRange. ...
– Marcucciboy2
Nov 14 '18 at 14:49
@Marcucciboy2 no, that will throw a not supported error.
– Nick
Nov 14 '18 at 15:04
2
2
Just a thought (not looked at improving the maths on it) - change the Top, Left, Height and Width to:
.Top = cell.Top - (cell.Offset(-1).Height / 2), .Left = cell.Left - (cell.Offset(, -1).Width / 4), .Height = cell.Height + (cell.Offset(-1).Height / 2) + (cell.Offset(1).Height / 2), .Width = cell.Width + (cell.Offset(, -1).Width / 4) + (cell.Offset(, 1).Width / 4) so the circle extends into the neighbouring cells.– Darren Bartrup-Cook
Nov 14 '18 at 13:33
Just a thought (not looked at improving the maths on it) - change the Top, Left, Height and Width to:
.Top = cell.Top - (cell.Offset(-1).Height / 2), .Left = cell.Left - (cell.Offset(, -1).Width / 4), .Height = cell.Height + (cell.Offset(-1).Height / 2) + (cell.Offset(1).Height / 2), .Width = cell.Width + (cell.Offset(, -1).Width / 4) + (cell.Offset(, 1).Width / 4) so the circle extends into the neighbouring cells.– Darren Bartrup-Cook
Nov 14 '18 at 13:33
I believe you can avoid the
select part of your code by just trimming down to With circ .ShapeRange. ...– Marcucciboy2
Nov 14 '18 at 14:49
I believe you can avoid the
select part of your code by just trimming down to With circ .ShapeRange. ...– Marcucciboy2
Nov 14 '18 at 14:49
@Marcucciboy2 no, that will throw a not supported error.
– Nick
Nov 14 '18 at 15:04
@Marcucciboy2 no, that will throw a not supported error.
– Nick
Nov 14 '18 at 15:04
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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.
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%2fstackoverflow.com%2fquestions%2f53300628%2fis-it-possible-to-have-excel-circle-the-cell-that-contains-todays-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
1
I bet you could have a shape anchored at the corner of the cell with today’s date. Wish I could take a crack at this one
– Marcucciboy2
Nov 14 '18 at 12:50
Does it have to be a circle, or could you use .Borders.LineStyle = xlContinuous?
– Cyril
Nov 14 '18 at 12:53