UtterAccess Discussion Forums > Problem With My Code

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

kapeller

Apr 21 2012, 06:23 PM

Hi

I seek assistance with the code below.

This is what Norie, one a UA member helped me with and it functions as required.

CODE

Sub ColorCells()
Dim R_From As Integer
Dim R_To As Integer
Dim R As Integer

    R_From = 11
    R_To = 150

    For R = R_From To R_To
        Rows(R).Select
        If Cells(R, 33) = "HIGH" Or Cells(R, 33) = "MEDIUM" Or Cells(R, 36) = "NON COMPLIANCE" Then
            Cells(R, "A").Value = "1"
            Selection.Font.Color = vbRed
        Else
            Selection.Font.Color = vbBlack
        End If
    Next R
End Sub

I want to have the code change the cells range to vbOrange and the value in Cells A to 2. My attempt is below, it obviously does not work?

CODE

Sub ColorCells()
Dim R_From As Integer
Dim R_To As Integer
Dim R As Integer

    R_From = 11
    R_To = 150

    For R = R_From To R_To
        Rows(R).Select
        If Cells(R, 33) = "HIGH" Or Cells(R, 36) = "NON COMPLIANCE" = Cells(R, "A").Value = "1" = Selection.Font.Color = vbRed _
           = Cells(R, 33) = "MEDIUM" Or Cells(R, 36) = "NON COMPLIANCE" = Cells(R, "A").Value = "2" = Selection.Font.Color = vbOrange Then
        Else
            Selection.Font.Color = vbBlack
        End If
    Next R
End Sub

Many thanks

norie

Apr 21 2012, 06:44 PM

Lou

The syntax isn't quite right.

VBA is going to see all of this as one long expression that it will then try and evaluate

CODE

Cells(R, 33) = "HIGH" Or Cells(R, 36) = "NON COMPLIANCE" = Cells(R, "A").Value = "1" = Selection.Font.Color = vbRed _
= Cells(R, 33) = "MEDIUM" Or Cells(R, 36) = "NON COMPLIANCE" = Cells(R, "A").Value = "2" = Selection.Font.Color = vbOrange

If it evaluates to True then nothing will happen, if it evaluates to False then the font will be coloured black.

I think I know what you want to do but I'm not sure.

Here's what I think it is.

If column 33 is 'HIGH' or column 36 is 'NON COMPLIANCE' then put 1 in column A and colour the font in the row red.
If column 33 is 'MEDIUM' or column 36 is 'NON COMPLIANCE' then put 2 in column A and colour the font in the row orange.

Otherwise, colour the font black.

Is that right?

Mind you there does seem to be a bit of an 'overlap', the 2 conditions could both be satisfied if column 36 is 'NON COMPLIANCE'.

Anway, I've changed the Or to And in this code and removed the Select/Selection.

CODE

Option Explicit

Sub ColorCells()

Dim R_From As Integer
Dim R_To As Integer
Dim R As Integer

    R_From = 11
    R_To = 150

    For R = R_From To R_To

        Select Case True

            Case Cells(R, 33) = "HIGH" And Cells(R, 36) = "NON COMPLIANCE"
                Cells(R, "A").Value = "1"
                Rows(R).Font.Color = vbRed
            Case Cells(R, 33) = "MEDIUM" And Cells(R, 36) = "NON COMPLIANCE"
                Cells(R, "A").Value = "2"
                Rows(R).Font.Color = rgbOrange
            Case Else
                Rows(R).Font.Color = vbBlack
        End Select

    Next R
End Sub

PS Sorry if I've completely misinterpreted what you are trying to do.

kapeller

Apr 21 2012, 07:11 PM

Hi Norie

No you have not misinterpreted what I am trying to do.

It was a change that I made in the database that is exported into excel.

As before many thanks for your help with this.

norie

Apr 21 2012, 08:19 PM

Lou

Quick question, does the code work?

It took me a few minutes to work out what I thought you were trying to do.

kapeller

Apr 21 2012, 08:52 PM

Hi Norie

I have just given it a go and it does not run.

Insteat of trying to explain I have attached the sheet with the exported data put in your code, but have not run it yet, I left that for you so you can see what is happening.

Many thanks

Click to view attachment

norie

Apr 21 2012, 09:27 PM

Lou

Which worksheet was active when you tried the code?

I just ran it with the 'RTO Performa(n)ce Details' worksheet active and it appeared to work.

I also tried it with the 'Data Sheet' worksheet active and the code did run but because 'Data Sheet' was active it basically did nothing.

That's because without a worksheet reference something like this refers to the active worksheet.

CODE

Cells(R, 33)

If that is the problem you can remedy it by adding a worksheet reference to make sure the code is referring to the correct worksheet.

CODE

Sub ColorCells()

Dim R_From As Integer
Dim R_To As Integer
Dim R As Integer

    R_From = 11
    R_To = 150

    For R = R_From To R_To

        With Worksheets("RTO Performace Details")
            Select Case True

                Case .Cells(R, 33) = "HIGH" And .Cells(R, 36) = "NON COMPLIANCE"
                    .Cells(R, "A").Value = "1"
                    .Rows(R).Font.Color = vbRed
                Case .Cells(R, 33) = "MEDIUM" And .Cells(R, 36) = "NON COMPLIANCE"
                    .Cells(R, "A").Value = "2"
                    .Rows(R).Font.Color = rgbOrange
                Case Else
                    .Rows(R).Font.Color = vbBlack
            End Select
        End With

    Next R
End Sub

Again, sorry if I've got the wrong end of the stick.

PS If that doesn't fix the problem then I'll have another look tomorrow.

kapeller

Apr 21 2012, 10:07 PM

Hi Norie

You are very good

I do not know why it did not work before, but now it does and that is all that matters.

One small item I changed the colour to vbMagenta because organge does not work. It may be my setting. I am very happy.

Once again many many thanks.

Have a drink or two

or something stronger

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.