Excel / VBA Consultancy

Free Training Videos

Free Spreadsheets

Excel / VBA Jobs

Navigation

New Excel Experts

milagroscanterbury63
shanelsosa9583420
tiffinybeaver135352

Current Excel / VBA Jobs

More jobs

Changing colors in Excel using VB

Submitted by mec1981 on 28 December, 2011 - 15:27

I have been asked to create a training spreadsheet at work that is to be colour coded to individual levels. I have entered the below VB string but have used educational guesses so far as I have no training in VB.

At present the Sheet cells have a validation list where members of the team can only select 1 of 5 skill set options, however the background colour does not automatically update unless you actually activate the cell after making your selection. Can you please advise how I can get this to automatically update with each change? Also can you explain how I get the font text to change to white if someone selects "Untrained N/A" from the options below?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim icolor As Integer
 
 
 
    If Not Intersect(Target, Range("C5:N250")) Is Nothing Then
 
        Select Case Target
 
            Case "Require Training"
 
                icolor = 3
 
            Case "Untrained N/A"
 
                icolor = 1
 
            Case "Under Development"
 
                icolor = 45
 
            Case "Can Use Unaided"
 
                icolor = 6
 
            Case "Able to Teach/Coach"
 
                icolor = 4
 
            Case Else
 
                'Whatever

        End Select
 
 
 
        Target.Interior.ColorIndex = icolor
 
    End If
 
Dim fcolor As Integer
 
 
End Sub

Thanks

Mark

mec1981's blog
Login or register to post comments
ShareThis
4122 reads

any reason you're not using

Submitted by Nick on 28 December, 2011 - 16:15.

any reason you're not using conditional formatting ?

excelexperts.com/Excel-Tips-Conditional-formatting

Hi Nick Conditional

Submitted by mec1981 on 28 December, 2011 - 16:18.

Hi Nick

Conditional Formatting is limited to 3 conditions whereas I have 5 conditions therefore have to use VB.

Regards
Mark

which version of Excel are

Submitted by Nick on 28 December, 2011 - 16:22.

which version of Excel are you using ?

Excel 2003 version

Submitted by mec1981 on 28 December, 2011 - 16:26.

Excel 2003 version 11.0.8341.0

... ok, here's what you need

Submitted by Nick on 28 December, 2011 - 16:27.

... ok, here's what you need to change your VBA to:

from this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

to this:

Private Sub Worksheet_Change(ByVal Target As Range)

Hi Nick, Thank you, that has

Submitted by mec1981 on 28 December, 2011 - 16:36.

Hi Nick,

Thank you, that has worked on all sheets for me apart from 1. I have an individual sheet for each employee and then we have a master sheet that I have a Vlookup function in each cell to pull through the latest developement stage.

By taking out the word Selection it does not change any background colours at all. Do you have any advice on this?

Mark

the change event is only

Submitted by Nick on 28 December, 2011 - 16:46.

the change event is only fired off when a user changes a cell value (and not when a formula is recalculated), so if you want the master to change too, you need to force it.

on your master sheet's macro code module, put this:

Private Sub Worksheet_Activate()
    For Each theCell In Range("A1:B2")
        Call Worksheet_Change(theCell)
    Next
End Sub

Notes:

- this assumes the vlookup range is "A1:B2"

- the code will fire off when you activate the summary worksheet.

Personally, I think it's an ugly thing to do, and I don't recommend implementing it.

Hi Nick, I will take your

Submitted by mec1981 on 28 December, 2011 - 16:50.