Change Colour of 3 Cells based on Value of Other Cell

**hazza147** · 11-17-2010, 12:07 PM

Hi,

I'm trying to create a Macro which will change the interior colour of 3 cells based on the value of another cell. I can't use Conditional Formatting as our Sales force use Office 2003 and therefore I can only have 3(4) different rules however I need 5. I have tried many attempts and scoured the net however have had no luck so far. Here is my best attempt at a Macro so far:


Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B1").Value = 1 Then
    
    Cells.Find(What:="baaa", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 8).Range("A1:A3").Select
    Selection.Interior.ColorIndex = 37
    
    End If
End Sub

You'll be able to see I am searching for a independent text string "baaa" and then once found offsetting by 8 cells to the right. I use independent text strings so that if somebody inserts a row it will still find and offset to the correct cell.

Does anyone see anything wrong with the above macro as it simply isn't working!

Kind Regards,
Harry Seager

**StephenR** · 11-17-2010, 12:11 PM

It works for me. But why in the worksheet change event? Is B1=1?

**ReportMaker** · 11-17-2010, 12:27 PM

Originally Posted by hazza147

Hi,

I'm trying to create a Macro which will change the interior colour of 3 cells based on the value of another cell. I can't use Conditional Formatting as our Sales force use Office 2003 and therefore I can only have 3(4) different rules however I need 5. I have tried many attempts and scoured the net however have had no luck so far. Here is my best attempt at a Macro so far:


Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B1").Value = 1 Then
    
    Cells.Find(What:="baaa", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 8).Range("A1:A3").Select
    Selection.Interior.ColorIndex = 37
    
    End If
End Sub

You'll be able to see I am searching for a independent text string "baaa" and then once found offsetting by 8 cells to the right. I use independent text strings so that if somebody inserts a row it will still find and offset to the correct cell.

Does anyone see anything wrong with the above macro as it simply isn't working!

Kind Regards,
Harry Seager

Hello

Here's some alternative code you can try:

Private Sub ConditionalFormat()
    
    ' Change these to whatever applys to your sheet.
    Const StartRow As Long = 1
    Const LastRow As Long = 8
    Const ColOffset As Long = 8
    Const SearchCol As Long = 1
    Const SearchFor As String = "baaa"
    
    Dim i As Long
    
    ' Loop through all rows.
    For i = StartRow To LastRow
    
        ' If current cell equals whatever you're looking for...
        If Cells(i, SearchCol).Value = SearchFor Then
        
            ' ...highlight it.
            Cells(i, SearchCol).Offset(0, 8).Interior.ColorIndex = 37
            
        End If
    Next i
    
End Sub

If yours works though use it.

Originally Posted by StephenR

It works for me. But why in the worksheet change event? Is B1=1?

This lad is right. Check if B1 = 1 (whatever that's about). Also if the sheet has a lot of data on it using the worksheet change could slow things horribly.

**hazza147** · 11-18-2010, 05:29 AM

Originally Posted by StephenR

It works for me. But why in the worksheet change event? Is B1=1?

The setup is as follows; I use a combo box which is linked to Cell B1. Therefore depending on what value is selected B1 will have a value from 1-5. Each different value from 1-5 needs a different colour and therefore I use 5 different macros ie. If B1=1 then..., if B1=2 then...

Unfortunately the code provided doesn't fulfill my needs... plus as a VBA amateur I'm struggling to understand what half of it means!

I use the search string 'baaa' to find the row location of where I would like highlighed a little like a Match function, then I offset by 8 columns to get to the box I need highlighting.

Thanks for your help so far.

**hazza147** · 11-18-2010, 05:57 AM

Problem solved. Used the following Macro in the worksheet:


Private Sub ListBox2_Click()
If Sheets("Main Screen (Beta)").Range("B1").Value = 1 Then
    
    Cells.Find(What:="baaa", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 8).Range("A1:A3").Select
    Selection.Interior.ColorIndex = 37
        
End If

If Sheets("Main Screen (Beta)").Range("B1").Value = 2 Then
    
    Cells.Find(What:="baaa", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 8).Range("A1:A3").Select
    Selection.Interior.ColorIndex = 34
        
End If
End Sub