How to change conditional formatting into VBA.

[bat18]

I have a spreadsheet where columns A:H have 3 different long conditional formats based on values in different columns producing a different colour depending on the value.

This is making the spreadsheet very slow and i could also do with introducing a 4th colour, is there a way I could move this all to VBA and when i enter my values i simply click a button to run the macro which will fill the cells with the correct colour.

2 questions: Will this stop the spreadsheet from performing slowly and what is the type of VBA to write. Any help would be really appreciated.

 

[bat18]

Yeah its vital they're different, the reason the values are in different columns is because I have placed them there specifically due to the nature of the value and where it has come from. I can cope with 3 colours though as the 4th set of values doesn't appear that often, but it would just make everything a lot easier if I could have a 4th colour.

 

[Jonmo1]

OK, so you want to take each value in columns A:H
Search columns U:X for that value.
If it is NOT found = no color
If it is in Column U = Color1
If it is in Column V = Color2
If it is in Column W = Color3
If it is in Column X = Color4

Is that about right?

 

[bat18]

Bit more complicated than that sadly.

I'm searching columns U:X and if one of those values match a value in column Y then the corresponding cells in columns A:H turn a certain colour

If it is NOT found = no color
If it is in Column U = Color1 (is put in corresponding cells in columns A:H)
If it is in Column V = Color2 ("")
If it is in Column W = Color3 ("")
If it is in Column X = Color4 ("")

 

[Jonmo1]

OK, so if
the value in Y2 is found in column U, than A2:H2 = Color1
the value in Y3 is found in column V, then A3:H3 = Color2
the value in Y4 is found in column W, then A4:H4 = Color3
the value in Y5 is found in columm X, then A5:H5 = Color4
the value in Y6 is NOT found anywhere, then A6:H6 = NO color


Now, the original question I had.
Do you want these colors to update automatically when you change values in the cells? If so, Which cells should trigger the code when they are changed?
Or do you want to only run the code manually from Tools - Macros ?

 

[Dan Waterloo]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iColor As Integer

If Target.Column = 25 Then
  If Target = Cells(Target.Row, 21) Then
    Range(Cells(Target.Row, 1), Cells(Target.Row, 8)).Interior.ColorIndex = 21
  ElseIf Target = Cells(Target.Row, 22) Then
    Range(Cells(Target.Row, 1), Cells(Target.Row, 8)).Interior.ColorIndex = 22
  ElseIf Target = Cells(Target.Row, 23) Then
    Range(Cells(Target.Row, 1), Cells(Target.Row, 8)).Interior.ColorIndex = 23
  ElseIf Target = Cells(Target.Row, 24) Then
    Range(Cells(Target.Row, 1), Cells(Target.Row, 8)).Interior.ColorIndex = 24
  End If
End If
End Sub

This changes colors only on the line that is currently affected. Experiment with colors to suit you.

 

[bat18]

Cheers this has been very useful,

Jonno you've got it slightly the wrong way round. The values in U:X are sat there all day long, I stick a value anywhere into column Y (place is irrelevant) and then it should match to a value in say U278. This will then trigger cells A278:H278 to turn a colour

 

[Dan Waterloo]

Note: The code I gave above works if you're just changing one cell at a time. But if you try to paste several values into column Y at one time it will choke. There's likely a work around if you need it.

And I need to add that you put this code on the sheet wehere you want it by right-clicking on the tab, selecting View Code, and then pasting it in.

 

[Jonmo1]

Try this, you'll need to do your own trial and error to get the desired colors.

Sub MyFormat()
Dim SearchRange As Range, ValRange As Range, c As Range
Dim MyCol As Long, LR As Long
Dim MyColor As Variant
LR = Cells.SpecialCells(xlCellTypeLastCell).Row
Set SearchRange = Range("U2:X" & LR)
Set ValRange = Range("Y2:Y" & LR)
For Each c In ValRange
    MyCol = 0
    On Error Resume Next
    MyCol = SearchRange.Find(c.Value, SearchRange(1, 1)).Column
    On Error GoTo 0
    Select Case MyCol
        Case 21: MyColor = 6 '<--Adjust these color #s as needed
        Case 22: MyColor = 17 '<--Adjust these color #s as needed
        Case 23: MyColor = 3 '<--Adjust these color #s as needed
        Case 24: MyColor = 23 '<--Adjust these color #s as needed
        Case Else: MyColor = xlNone
    End Select
    Cells(c.Row, 1).Resize(1, 8).Interior.ColorIndex = MyColor
Next c
End Sub

 

[Dan Waterloo]

Wow. I just realized that I didn't bother reading the clarifying posts. Okay. So please disregard my previous entries!