highlight current cell with or without conditional formating

  • From searching old post I found this code by Derek and Thomach. This works great for highlighing current cell and return cell to orginal color. the What I am now wanting to do if the the cell has an active conditional format also highlight that cell. Currently if the cell has an active conditional format nothing happens




    Thanks Mark

  • Re: highlight current cell with or without conditional formating


    A Conditional format overrides any other colors. To do what you want, the conditional formating would need to be removed, stored, and then returned when the selection changed. There are several things that could interfer with the restoration causing it to be lost. Do you want to risk that?

  • Re: highlight current cell with or without conditional formating


    Derek, it looks like the small benift for the color change is not worth the risk.


    Thank you for your help.


    Mark

  • Re: highlight current cell with or without conditional formating


    If you're not using all 3 conditional formats, you could just add a new cond format to change the cell to the highlight color if a hidden column value equals one. Then your highlight macro just needs to highlight the row and set the corresponding value in the row to 1.


    If you go that route, you might even consider modifying the macro so it just toggles the hidden column value to 1 and allow conditional formats to handle all the highlighting.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: highlight current cell with or without conditional formating


    One last thought then...


    Rather than change the background of the cell, just have your macro select the entire row but keep the activecell active within the range selection.


    Essentially by selecting the entire row you're highlighting it. Doesn't change any formats and would provide similar functionality.


    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Target.EntireRow.Select
        Target.Activate
    End Sub

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: highlight current cell with or without conditional formating


    Your idea works on the whole row being highlighted. I still like being able to just highlight the working cell. What about instead of highlighting the cell just hightlight the Border in another color. Would this work with conditional formating as long as one is not changing the borders with a conditional format??


    Mark

  • Re: highlight current cell with or without conditional formating


    Perhaps... I'll let you test that theory.


    The selected cell is going to have a thick selected range border around it already, is a different border color going to make a huge difference?

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: highlight current cell with or without conditional formating


    Here is an example to get you started. I agree with Aaron, that there is not much (any) utility to it. Since the cell is selected, the border color that shows will not be the same as indicated in the macro. This example also doesn't preserve all of the aspects of existing borders.
    [vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static rngPrev As Range, PrevColor As Integer
    Dim TempColor As Integer
    TempColor = Target.Cells(1, 1).Interior.ColorIndex

    If Not rngPrev Is Nothing Then
    If PrevColor <> xlLineStyleNone Then
    rngPrev.BorderAround ColorIndex:=PrevColor
    Else
    rngPrev.Borders.LineStyle = xlLineStyleNone
    End If
    End If
    PrevColor = TempColor

    Target.BorderAround Weight:=xlThick, ColorIndex:=3
    Set rngPrev = Target


    End Sub
    [/vba]

  • Re: highlight current cell with or without conditional formating


    HI
    Thanks for your previous message:




    I know that it was posted 4 1/2 years ago, but it has just worked for me :)


    I have a spreadsheet with many conditional formats and after several hours checking out other "solutions" only your idea is working for me ... thanks.


    Ignorant question ... Do I have to post the code into each VBAProject / microsoft excel object > sheet#(??)
    As I would like the feature to work on ALL sheets is there a place to store it once only, and for it to apply to all sheets?


    Thanks
    Mark

  • Re: highlight current cell with or without conditional formating


    Mark,

    Welcome to Ozgrid. While we are glad you found this thread to contain a solution to your problem, please do not post questions in old threads. Please start your own thread with an appropriate Search Friendly Key Word title and refer back to previous threads when necessary. Thank you.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!