Excel question

General Computing Coding and Design
1

I really don’t know if this is do-able or not, I don’t know how best to search
for what i’m after that provides me usefull info on what avenue to follow.

In short is there a way on saying i.e. if cell XX = value then display value but colour the cell background with colour xx

The idea being importing a CSV file generated by our main Movex system
then highlighting in block form values that differ from an auto generated base average in a traffic light type system where if the value is x% then colour Green if y% colour Yellow if z% colour red.

2

I think what your after is conditional formating, BUT you can only have 3 options for each cell, but it’s doable. I havent got office installed here so I can’t tell you where the option is , sorry.

Damski

3

That’s the one :slight_smile: looks like it maybe a bit long winded to set up
but indeed that’s the effect I’m after.

Just maybe a ballache to set each and every cell up to ref the right data.

Thx Damski :thumbsup:

4

NP is the type of stuff I set up at work all the time.

5

I used a colour change macro in the Rosetta stats sheet I used to run. It changed the colour of the font, but I’m guessing the principle is the same:

Sub colourchange()
’
’ colourchange Macro
’ Macro recorded 24/04/05 by Andrew Young
’
’

For x = 1 To 300

If Cells(x, 6).Value < “0” Then
Cells(x, 6).Font.Color = RGB(255, 0, 0)
End If
If Cells(x, 6).Value > “0” Then
Cells(x, 6).Font.Color = RGB(0, 128, 0)
End If
If Cells(x, 6).Value = “0” Then
Cells(x, 6).Font.Color = RGB(0, 0, 0)
End If
If Cells(x, 6).Value = “NEW” Then
Cells(x, 6).Font.Color = RGB(0, 0, 255)
End If
If Cells(x, 6).Value = “Position” Then
Cells(x, 6).Font.Color = RGB(0, 0, 0)
End If
If Cells(x, 6).Value = “Change” Then
Cells(x, 6).Font.Color = RGB(0, 0, 0)
End If
If Cells(x, 6).Value = “” Then
Cells(x, 6).Font.Color = RGB(0, 0, 0)
End If
Next x

End Sub

I’m guessing the solution could be similar, but I can’t find the right syntax. I’ll search and get back to you.

6

You could use:
Cells(x,y).Interior.ColorIndex = Number
or
Cells(x,y).Interior.Color = RGB(r,g,b)

7

Cheers Kev!! :thumbsup: The “RGB(r,g,b)” does not work in my Office 97 when I use say RGB(255,255,0). It comes back with error code 1004. Works OK with the number though. I’ve also had to put in a line to change the font colour to a contrasting colour so that you can read the cell value.

Sub colourchange()
’
’ colourchange Macro
’ Macro recorded 24/04/05 by Andrew Young

For x = 1 To 300

If Cells(x, 6).Value < “0” Then ’ the 6 in the brackets refers to the 6th column in the spreadsheet, change it to whatever you want
Cells(x, 6).Interior.ColorIndex = 3
Cells(x, 6).Font.Color = 16777215
End If
Next x
End Sub

and then just add different “If…End If” statement for the different cell states inside the For - Next statement(green background, white text for positive values, white background, black text for zero values, etc.)

Tested it, it works OK

8

You get that error by using the colorindex property and not color.

9

Ah! sorted. Thanks again Kev. I’m learning more every time I come here.

10

I think we all do.