Conditional Formatting

**Bnecrush1** · 02-04-2021, 10:49 AM

I have a report that I need to do some conditional formatting on. The formatting needs to be based off of two fields.

The first field is a type, the second is a number.
The Type field has three choices C, T, V
The number is between 0 and 100

If [Type] = C and [Number] is <30 [Number] highlighted red
If [Type] = C and [Number] is >30 And <50 [Number] Highlighted Yellow

If [Type] = C and [Number] is >50 [Number] Highlighted Green
If [Type] = T and [Number] is <10 [Number] highlighted red
If [Type] = T and [Number] is >10 And <30 [Number] Highlighted Yellow
If [Type] = T and [Number] is >30 [Number] Highlighted Green
If [Type] = V and [Number] is <40 [Number] highlighted red
If [Type] = V and [Number] is >40 And <70 [Number] Highlighted Yellow
If [Type] = V and [Number] is >70 [Number] Highlighted Green

What is the best way to do this?

THANKS!!

**Welshgasman** · 02-04-2021, 10:51 AM

With all that, I'd write a function to do it.

**Bnecrush1** · 02-04-2021, 12:08 PM

Something along this:?

Private Sub Form_Current()

If Floor_Type = Concrete And AccessTotalsAoMR < 15 Then
Floor_Type.BackColor = vbRed

Else
If Floor_Type = Concrete And AccessTotalsAoMR < 15 And > 30Then
Floor_Type.BackColor = vbYellow
Else
If Floor_Type = Concrete And AccessTotalsAoMR > 30 Then
Floor_Type.BackColor = vbGreen
Else
If Floor_Type = VCT And AccessTotalsAoMR < 60 Then
Floor_Type.BackColor = vbRed
Else
If Floor_Type = VCT And AccessTotalsAoMR > 61 And < 80 Then
Floor_Type.BackColor = vbYellow
Else
If Floor_Type = VCT And AccessTotalsAoMR > 80 Then
Floor_Type.BackColor = vbGreen
Else

**Welshgasman** · 02-04-2021, 12:22 PM

I'd probably use nested Case statements. If you are checking strings you need to surround with "
I'd probably set some variables ColourMin, and ColourMax for each Type then compare for the colour.

You also have gaps in your logic with the ranges?

**davegri** · 02-04-2021, 12:36 PM

Agree with gasman. Hate those nested IF-THEN-ENDIF. Try this:

Code:

Select Case Floor_Type
    Case "Concrete"
        Select Case AccessTotalsAoMR
            Case < 15
                Floor_Type.Backcolor = vbRed
            Case > 30
                Floor_Type.Backcolor= vbGreen
            Case Else
                Floor_Type.Backcolor=vbYellow
        End Select
    Case "VCT"    
        Select Case AccessTotalsAoMR
            Case < 60
                Floor_Type.Backcolor = vbRed
            Case > 80
                Floor_Type.Backcolor = vbGreen
            Case Else
                Floor_Type.Backcolor = vbYellow
        End Select
End Select

You might need to adjust the comparators to be >= or <=
or maybe change the numeric literals up or down by 1 to get the results you want.

EDIT: Reading your posts again, it's not clear about Floor_Type values.
If you need to account for additional Types, you can easily add another Select Case ... End Select to include them.

**Bnecrush1** · 02-04-2021, 01:29 PM

Gave it a try and it didn't see to work - must be I have missed somthing?

Code:

Private Sub Form_Current()
Select Case Floor_Type
    Case "Concrete"
        Select Case AoMR
            Case Is < 15
                AoMR.BackColor = vbRed
            Case Is >= 30
                AoMR.BackColor = vbGreen
            Case Else
                AoMR.BackColor = vbYellow
        End Select
    Case "VCT"
        Select Case AoMR
            Case Is < 60
                AoMR.BackColor = vbRed
            Case Is >= 80
                AoMR.BackColor = vbGreen
            Case Else
                AoMR.BackColor = vbYellow
        End Select


    Case "ConcreteS"
        Select Case AoMR
            Case Is < 50
                AoMR.BackColor = vbRed
            Case Is >= 70
                AoMR.BackColor = vbGreen
            Case Else
                AoMR.BackColor = vbYellow
        End Select


    Case "Ceramic Tile"
        Select Case AoMR
            Case Is < 50
                AoMR.BackColor = vbRed
            Case Is >= 70
                AoMR.BackColor = vbGreen
            Case Else
                AoMR.BackColor = vbYellow




    End Select




End Sub

**Welshgasman** · 02-04-2021, 01:33 PM

At least an End Select ?

I doubt that will even compile?

Walk through the code line by line with F8 and see what it does.

**davegri** · 02-04-2021, 02:32 PM

Need another END SELECT just before END SUB.
You have the possible values of Floor_Type as

"Concrete"
"ConcreteS"
"VCT"
"Ceramic Tile"

Is that correct?

**Bnecrush1** · 02-04-2021, 03:11 PM

That is correct.

I added the END SELECT and it still didn't work.
Currently some of the Floor_Type entries are blank. That will not be in the future - could that be causing an issue?
Actually had it remove all the data once from the form itself after I changed the view.

**Welshgasman** · 02-04-2021, 03:19 PM

You said in the original post that you wanted to format a report?

Yet you are creating code in a form Current event?

**davegri** · 02-04-2021, 03:20 PM

Originally Posted by Bnecrush1

That is correct.

I added the END SELECT and it still didn't work.
Currently some of the Floor_Type entries are blank. That will not be in the future - could that be causing an issue?
Actually had it remove all the data once from the form itself after I changed the view.

I think your best bet is to upload the DB here so that someone can take a look. Then instead of suggested possible fixes based on speculation, you will get solid advice.

**Bnecrush1** · 02-04-2021, 03:24 PM

My Bad: It is a report:

Code:

Private Sub Report_Current()


Select Case Floor_Type
    Case "Concrete"
        Select Case AoMR
            Case Is < 15
                AoMR.BackColor = vbRed
            Case Is >= 30
                AoMR.BackColor = vbGreen
            Case Else
                AoMR.BackColor = vbYellow
        End Select
        
    Case "VCT"
        Select Case AoMR
            Case Is < 60
                AoMR.BackColor = vbRed
            Case Is >= 80
                AoMR.BackColor = vbGreen
            Case Else
                AoMR.BackColor = vbYellow
        End Select


    Case "ConcreteS"
        Select Case AoMR
            Case Is < 50
                AoMR.BackColor = vbRed
            Case Is >= 70
                AoMR.BackColor = vbGreen
            Case Else
                AoMR.BackColor = vbYellow
        End Select


    Case "Ceramic Tile"
        Select Case AoMR
            Case Is < 50
                AoMR.BackColor = vbRed
            Case Is >= 70
                AoMR.BackColor = vbGreen
            Case Else
                AoMR.BackColor = vbYellow
        End Select
     
        
End Select






End Sub

**Welshgasman** · 02-04-2021, 03:49 PM

I would hazard a guess that the code needs to be in the Detail On Format event?

Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

**Bnecrush1** · 02-05-2021, 09:48 AM

Thanks everyone - got it to work on the "On Load"

**Welshgasman** · 02-05-2021, 09:59 AM

Originally Posted by Bnecrush1

Thanks everyone - got it to work on the "On Load"

That event only happens once?

Conditional Formatting

Thread Tools

Conditional Formatting

Similar Threads

conditional formatting

Conditional formatting problem - formatting not initially displayed

Need Help With Conditional Formatting

First Post! On Creating Conditional Reports (No, Not Conditional Formatting)

Conditional formatting

Posting Permissions