Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBA conditional Formatting Excel 2003 1

Status
Not open for further replies.
chriscusick

chriscusick

Technical User
Apr 23, 2013
25
GB
Hi,

I was asked to post here by SkipVought, as I posted this in another forum but think it was the wrong one.

I have a spreadsheet that I need to do 6 conditional formats on. I realize this needs to be done in VBA but I have NEVER used VBA before so I'm at a loss.

What I need the vba to do is check the text in cell A1 and if it meets a certain criteria colour cells a1 - j1 a certain colour. The code needs to change the cells for the relevant criteria.

The other criteria are also in cell a1, in drop down form.

If it helps, the criteria are:

"To be booked", "Booked", "Done", "Invoiced", "Paid", "Cancelled"

So, for example, if cell A1 = "DONE" then cells A1-J1 would turn green. If Cell A1 = "Cancelled" it would color the cells Red, so on and so forth.

I was asked by SkipVought to record a macro, which is below this body of text, but I am not sure if it is correctly recorded in the sense that it changed the colours but there is no information on the status change (the criteria I need the formatting for)

The code would need to go down as far as row 150, as this is as many rows as i would be using

Is there anyone who could actually write this out for me as I have NO programming experience in VBA and wouldn't even know where to start adapting one of the examples from other threads.

Thank you all in advance


Sub Macro3()
'
' Macro3 Macro
' Macro recorded 23/04/2013 by Chris
'

'
Range("A1:J1").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("A1:J1").Select
Selection.Interior.ColorIndex = 44
Selection.Interior.ColorIndex = 35
Selection.Interior.ColorIndex = 37
Selection.Interior.ColorIndex = 4
Selection.Interior.ColorIndex = 3
Selection.Font.Bold = True
End Sub
 
Sort by date Sort by votes
You'll have to put the correct color index with each value.

You will also have to enable macros when you open your workbook after this code is saved.

Paste this code in the SHEET CODE WINDOW. Right the Sheet Tab and select View Code

This is event driven code, based on what is entered in column A
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iColorIndex As Integer
    
    If Target.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Columns(1)) Is Nothing Then
        Select Case UCase(Target.Value)
            Case "TO BE BOOKED"
                iColorIndex = 44
            Case "BOOKED"
                iColorIndex = 35
            Case "DONE"
                iColorIndex = 37
            Case "INVOICED"
                iColorIndex = 4
            Case "PAID"
                iColorIndex = 3
            Case "CANCELLED"
                iColorIndex = 36
            Case Else
                iColorIndex = xlNone
        End Select
        Intersect(Target.EntireRow, Range("A1:J1").EntireColumn).Interior.ColorIndex = iColorIndex
    End If
End Sub

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Thank you SO much for that Skip,

I did have to change the colors round but it works perfectly.

My only other question now is what do I add to this to make the text go BOLD when using the cancelled criteria? As this is going to be red it will make the text a bit easier to see?

Thank again for your help with this [bigsmile]
 
Upvote 0 Downvote
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iColorIndex As Integer, bBold As Boolean
    
    If Target.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Columns(1)) Is Nothing Then
    
        bBold = False
        
        Select Case UCase(Target.Value)
            Case "TO BE BOOKED"
                iColorIndex = 44
            Case "BOOKED"
                iColorIndex = 35
            Case "DONE"
                iColorIndex = 37
            Case "INVOICED"
                iColorIndex = 4
            Case "PAID"
                iColorIndex = 3
            Case "CANCELLED"
                bBold = True
                iColorIndex = 36
            Case Else
                iColorIndex = xlNone
        End Select
        
        With Intersect(Target.EntireRow, Range("A1:J1").EntireColumn)
            .Font.Bold = bBold
            .Interior.ColorIndex = iColorIndex
        End With
        
    End If
End Sub

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Thank you so much Skip :)

I would like to add that I posted this question on another forum and they simply directed me to a different site to "adapt" one of the vba's there.

I am glad that I came across this site with people who are helpful enough to understand when I say I have NO VBA experience they don't just throw me to another site and off the deep end, expecting me to adapt coding I have no clue about.

I will be recommending you guys to anyone with Excel and VBA questions in future :)

 
Upvote 0 Downvote
Glad you had a good experience here. This IS a great site.

My objective is not not only help members get an immediate answer, but also to help them understand, if they so desire, to begin learning how to do some of this stuff themselves. Kind of the "give a man a fish," "teach a man to fish" approch.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
You certainly do a great job here!

I am very grateful for the help and now I can sit down and start picking through the code you provided (visually of course) and start understanding how the lines of code achieve what they do to help me understand how VBA coding actually works.

I appreciate getting people to learn these things but its much easier when you are learning from something written for your own spreadsheet as you know what the outcome is and then can work through the lines to see HOW it works, rather than saying "Here's a website with coding, figure it out".

So once again, thank you so much for this. Time for me to go and pick it apart...visually.
 
Upvote 0 Downvote
Post back any time with your questions.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
Run PowerShell Commands from Excel VBA
Replies
14
Views
1K
dhookom
dhookom
thanhthanh12
  • Locked
  • Question
Help vba
Replies
3
Views
301
combo
combo
Andrzejek
  • Locked
  • Question
Outlook VBA - Trust access
Replies
3
Views
421
hermanlaksko
hermanlaksko
SLW89
  • Locked
  • Question
VBA newbie needs help
Replies
4
Views
278
Andrzejek
Andrzejek
remeng
  • Locked
  • Question
Excel VBA to Pass Var between file macros after var changes
Replies
13
Views
289
mikrom
mikrom

Part and Inventory Search

Sponsor

Back
Top