Highlight cells then perform action

J

jasonb75

Well-known Member

Joined

Dec 30, 2008

Messages

15,507

Office Version

1. 365

Platform

1. Windows

• Aug 24, 2014

• #2

If the cells are empty then type as normal, then hold ctrl and press enter.

 

Upvote 0

M

MickG

MrExcel MVP

Joined

Jan 9, 2008

Messages

14,841

• Aug 24, 2014

• #3

Try this:-
Colour Yellow target Range , Code limited to Columns "A to E"

Code:

Option Explicit
Dim R As Range
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, R) Is Nothing Then
    R.Value = Target.Value
    Set R = Nothing
End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Columns("A:E")) Is Nothing Then
    Target.Interior.ColorIndex = 6
        If Target.Count > 1 Then Set R = Target
End If
End Sub

 

Upvote 0

M

mistersteve

Board Regular

Joined

Aug 18, 2014

Messages

110

Office Version

1. 365

• Aug 24, 2014

• #4

Thanks Mick, Ill give it a go

Steve T

 

Upvote 0

M

mistersteve

Board Regular

Joined

Aug 18, 2014

Messages

110

Office Version

1. 365

• Aug 24, 2014

• #5

Sometimes I am not good at explaining myself.

so in detail:

I have a calendar, when I want to fill in someone's "holiday, "Half-day" or "sick"

I would like to drag the mouse over the days (which would then be highlighted).

And type the character (only once) "H", "HD", "S".

The cells would then show the character in each and be coloured too.

Hope I make better sense

Steve T

 

Upvote 0

J

jasonb75

Well-known Member

Joined

Dec 30, 2008

Messages

15,507

Office Version

1. 365

Platform

1. Windows

• Aug 24, 2014

• #6

For entering what you type in all selected cells see my original suggestion.

If you want them coloured to highlight the various entries then conditional formatting should do it.

 

Upvote 0

M

mistersteve

Board Regular

Joined

Aug 18, 2014

Messages

110

Office Version

1. 365

• Aug 25, 2014

• #7

Jason, you are a star.

Now can I make this idiot proof? you see I wont be entering the data, someone else will.

Could this be done by highlighting the cells, then pressing a separate button to enter the "h" onto all the cells in one go.

Maybe a macro?

Steve T

 

Upvote 0

J

jasonb75

Well-known Member

Joined

Dec 30, 2008

Messages

15,507

Office Version

1. 365

Platform

1. Windows

• Aug 25, 2014

• #8

Resistant maybe, but idiot proof is virtually impossible. You could enter something like

Code:

Selection.Value ="h"

in the button code, but if the user doesn't approve the security warning to enable macros then the button will not work.

 

Upvote 0

M

mistersteve

Board Regular

Joined

Aug 18, 2014

Messages

110

Office Version

1. 365

• Aug 25, 2014

• #9

Hi Jason,

I m the one that sets up the security levels

So that wont be an issue

Steve T

 

Upvote 0

M

mistersteve

Board Regular

Joined

Aug 18, 2014

Messages

110

Office Version

1. 365

• Aug 26, 2014

• #10

Just to say,

Thanks to all for advice.

With your input I was able to solve my problem

Steve T

 

Upvote 0