Results 1 to 3 of 3

Thread: add an addition cell colour to coding

  1. #1
    Member
    Join Date
    May 2013
    Posts
    84
    Rep Power
    12

    add an addition cell colour to coding

    Hi

    Could anybody tell me how to add an additional colour to this code
    at the moment if text is entered with N-SIDE all the related cells will go yellow
    I have been trying to add it the text is ADD then they should turn pink.

    This is probably simple but not to me





    Code:
    Sub shadeCells(rangeToShade As Range, Target_Name As String)
        Dim Shaded(1 To 9) As Long, i As Integer, j As Integer
        Dim myColor As Long
        
        If UCase(Left(Target_Name, 6)) = "N-SIDE" Then
            myColor = 65535
        Else
            myColor = 4050606
        End If
        
        'Set the shading color numbers for each column
        Shaded(1) = myColor 'Col A
        Shaded(2) = myColor 'Col B
        Shaded(3) = myColor 'Col C
        Shaded(4) = myColor 'Col D
        Shaded(5) = myColor 'Col E
        Shaded(6) = myColor 'Col F
        Shaded(7) = myColor 'Col G
        Shaded(8) = myColor 'Col H
        Shaded(9) = myColor 'Col I
    
        For j = 1 To rangeToShade.Rows.Count
            For i = 1 To 9
                Cells(rangeToShade.Rows(j).Row, i).Interior.Color = Shaded(i)
            Next
        Next
    End Sub

    Thank you for looking

    Peter

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Code:
    If UCase(Left(Target_Name, 6)) = "N-SIDE" Then
        myColor = 65535
    ElseIf UCase(Left(Target_Name, 3)) = "ADD" Then
        myColor = 13353215 'Pink
    Else
        myColor = 4050606
    End If
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Member p45cal's Avatar
    Join Date
    Oct 2013
    Posts
    94
    Rep Power
    12
    Since there is only one colour, you don't need to set up arrays, nor colour the cells one by one, and if you want to add more colours later a select case construct is easier to add to:
    Code:
    Sub shadeCells(rangeToShade As Range, Target_Name As String)
    Dim myColor As Long
    Select Case True
      Case UCase(Left(Target_Name, 6)) = "N-SIDE": myColor = 65535
      Case UCase(Left(Target_Name, 3)) = "ADD": myColor = 13353215
      'Case UCase(Left(Target_Name, 14)) = "SOMETHING ELSE": myColor = 9999' add more if you want.
      Case Else: myColor = 4050606
    End Select
    rangeToShade.Resize(, 9).Interior.Color = myColor
    End Sub

Similar Threads

  1. Replies: 1
    Last Post: 06-09-2014, 09:58 AM
  2. change table top row to a different colour with html code
    By peter renton in forum Excel Help
    Replies: 2
    Last Post: 02-17-2014, 08:08 PM
  3. A code to show colour in cell from list
    By rodneykaye in forum Excel Help
    Replies: 4
    Last Post: 10-18-2013, 03:56 PM
  4. Replies: 5
    Last Post: 04-07-2013, 05:11 PM
  5. Add Control To Right-Click Cell Context Menu
    By Rasm in forum Excel Help
    Replies: 3
    Last Post: 04-17-2011, 08:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •