Highlight cell based on position of active cell

EssKayKay

Board Regular
Joined
Jan 5, 2003
Messages
202
Office Version
  1. 2007
Platform
  1. Windows
I’m looking for a little more assistance with this if you please. I have posted this elsewhere on the web with some success but am still experiencing some problems. If multi-posts are discouraged or even disallowed please disregard this inquiry.

Is it possible change the color or highlight a cell based on the position of the cursor/active cell?

Example: If the active cell is I36, I want C36 to change color or be highlighted in some way (e.g., bold text). As you cursor down column “I” the highlighted cell in column “C” changes corresponding to the row the cursor is in.

Any suggestions would be appreciated.

Thanks,
Steve K.

EDIT- I tried to post a screen shot of what I want but no luck.
 
Last edited:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi EssKayKay,

You could try something like this in the Worksheet, Selection Change event...

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    If Not Intersect(Target, Range("I1:I10")) Is Nothing Then
        
        With Sheet1.Range("C1:C10").Font
            .Bold = False
            .Size = 11
            .ColorIndex = 0
        End With
        
        With Sheet1.Range("C" & Target.Row).Font
            .Bold = True
            .Size = 14
            .ColorIndex = 3
        End With
    
    End If


End Sub

You will have to change the ranges to suit your needs but it should work as is on rows 1-10 on columns "I" and "C".

Hope that helps,

Doug
 
Upvote 0
Doug,

Thanks for your quick response. For the most part your code is doing what I want. However there is one thing I forgot to mention (my bad). I have the worksheet protected.

There are numerous cells “unlocked” where the user can input data but most of the cells are locked. I unlocked cells (C33:C1500) and (I33:I1500) which are the ones we are addressing in the code. However, with the worksheet protected, I receive a “Run-time error ‘1004’ – Unable to set Bold property for Font class”. If I unprotect the worksheet all works fine. Is it possible run your code with the worksheet protected?

Steve K.
 
Upvote 0
No problem Steve. Before the if statement put "Sheet1.Unprotect" and after the End If put "Sheet1.Protect". You may be able to use Activesheet instead of Sheet1. If you use a password, google how to include it in the added lines.

Doug
 
Upvote 0
I will try that. I may have found something else. When I enable protection I have the option of checking "Format Cells" which seems to allow font modification.
 
Upvote 0
I'll have to look at this closer tomorrow. I'll keep you posted.

again, thanks Doug
 
Upvote 0
I have got this working pretty well - I'm satisfied. Thank you Doug for your assistance - much appreciated...
 
Upvote 0
I have posted this elsewhere on the web with some success but am still experiencing some problems. If multi-posts are discouraged or even disallowed please disregard this inquiry.
For future reference-
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,734
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top