Excel Conditional Formatting

[jam.]

Thanks for not being put off by the almost too exciting title..

I use a large excel spreadsheet at work for tracking a lot of data - it was a mess before but I’m now sorting it out and improving it.

Im nearly there - but I could do with one thing if anyone could assist?

At the moment, some cells contain dates which are underlined when the task is completed or the day has elapsed. This is done manually and would like to keep this way for now.

What I would like is to add some conditional formatting to a cell once it’s underlined - ideally icons. Ive used these throughout the sheet already but I can’t figure out if or how this is possible with underlined text? Google isn’t being helpful.


Any ideas you beautiful people?

 

[Sir Nancy Flowers]

Our resident spreadsheet genius - @MarkCup

The floor is all yours Mark...

 

[Oggy997]

This needs to be done as a macro.

Is this an option ? I can write the code if it is.

 

[Oggy997]

Sorry, I may have misunderstood.

User selects underline font format
<Excel adds an icon>

?

 

[jam.]

Sorry, I may have misunderstood.

User selects underline font format
<Excel adds an icon>

?

Yeah that’s it thanks - I have a load of the circled tick and crosses throughout so would like it to match those.

 

[jam.]

Yo.

So, I’ve kind of kiboshed the idea of adding icons to cells with underlined text for now.

But I’ve been playing about with making this sheet work even better, and easier for anybody else to pick up.

This is where I’m now up to..

Essentially this worksheet contains one main sheet with details of all previous, current and future projects I am managing - and all information/requirements about them.
When a new project is added to the main sheet, a new tab is created (copied from a template) that, when the project number is entered in a cell (C2) - populates this sheet with a summary of info from the main sheet via a metric s**t tonne of VLOOKUPs.

That all works well - I’ve now added a bit of VBA code to the template sheet that renames the new project sheet - to the project number, once added to the cell. Quite slick.

Now, this is working great and does what’s required really well. However, throughout the course of the life of a project - it’s status is changed in a cell on the main sheet (there are 30 numbered stages) , this is also echoed in the project’s sheet (I3).
Now going back to conditional formatting, whenever each of these projects it assigned a new status - the status cell changes colour - to highlight the importance of the task required/make sure it’s not missed/to quickly differentiate it from other projects.
when this project is assigned a new colour as above - I change the project sheet colour to match this - so I have a nice rainbow selection of sheets, that I can quickly refer to and theoretically know what I’d need to do before clicking.
This works ok, but I want to automate this - so that the sheet automagically changes colour once the status is changed.

I’ve found some Vb code to sort of do this, but I’m struggling with naming of vbcolors and how to use this code along side the sheet renaming code.

This is the vba code im using to rename the project sheets;

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("A1")
If Target = "" Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)
Exit Sub
End Sub

And this is the code I’ve found to change the sheet tab colour;

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    If Target.Address = "$A$1" Then
        Select Case Target.Value
        Case "False"
            Me.Tab.Color = vbRed
        Case "True"
            Me.Tab.Color = vbGreen
        Case Else
            Me.Tab.Color = vbBlue
        End Select
    End If
End Sub

any ideas?
🤞🤞🤞

 

[jam.]

This is what the sheets look like now after manually changing to match the status colour.
I'd like to automate it as this likely is going to start ramping up soon so I could do with something to speed/automate the process. Maybe..🤞