Highlight cell based on position of active cell

[EssKayKay]

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.

 

[duggie33]

Hi EssKayKay,

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

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

 

[EssKayKay]

Deleted...

 

[EssKayKay]

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.

 

[duggie33]

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

 

[EssKayKay]

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.

 

[EssKayKay]

I'll have to look at this closer tomorrow. I'll keep you posted.

again, thanks Doug

 

[EssKayKay]

I have got this working pretty well - I'm satisfied. Thank you Doug for your assistance - much appreciated...

 

[duggie33]

You are welcome Steve.

 

[Fluff]

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.