Conditional format base on varying data

[Gizdale]

Good afternoon.
I am creating a script that will upload a .csv file into Excel, name the uploaded data as a range, typically the month, this is then averaged with a high and low values assigned, typically +/_ 5 of the average respectivly.
I will then apply a format blue for higher than average values, red for lower.
Getting this to function with one set of values to apply the condition to is no problem. Given the condition rules chmage on a monthly basis I have tried various methods if statement, case and so on the latest attempt is shown below
Private Sub CommandButton1_Click()
Dim rng
Dim cell As Range
Dim selectedRng As String
Dim High
Dim Low
selectedRng = InputBox("Enter your range")
Set rng = Range(selectedRng)
Select Case selectedRng
Case October
Range("o63").Value = High
Range("o62").Value = Low
Case November
Range("r63").Value = High
Range("r62").Value = Low
Case December
Range("v63").Value = High
Range("v62").Value = Low
End Select
For Each cell In rng

If Value > High Then
cell.Font.ColorIndex = 5
ElseIf Value < Low Then
cell.Font.ColorIndex = 3
End If
Next cell
End Sub

All it does is colour the text blue - pointer etc will be gratfully received

Regards

 

[Jerry Sullivan]

Hello and Welcome,

This code looks like it will need a lot of revising to do what you are describing.

However, if you are interested in learning VBA this is a good project to work through.
I'd offer these few pointers:

1 . Start your code module with the statement:

Option Explicit

When you do that, the compiler will alert you to many errors prior to actually running your code. In your example, the statement Case October will generate a compiler error since there is no symbol named October defined. You probably want to change that to Case "October".

2. Define your data types explicitly. For example, use

Dim rng as Range

If you don't define a data type, VBA will assume that you want a Variant.
The Option Explicit statement will require you define your data types. When you do use explicit data types you'll discover and be able to fix some errors that the Variant data type might have masked with unintended results.

3. Finally, I'd suggest you break your project down into smaller parts and use the Msgbox or Immediate Window in the VB Editor to confirm the code is doing what you want it to at each step. Perhaps start with something like this to develop your Select Case block:

Option Explicit
 
Private Sub CommandButton1_Click()
    Dim strInput As String
    strInput = InputBox("Enter Month")
    Select Case strInput
        Case "October"
            MsgBox "Match found: You entered October"
        Case "November"
            MsgBox "Match found: You entered November"
        Case Else
            MsgBox "Match not found. You entered: " & strInput
    End Select
End Sub

When you develop each step like this, you can typically use some trial and error to get each subsequent part to work as well.

Please give this a try and post to this thread if you get stuck anywhere.

Good luck!

 

[Gizdale]

Many thanks for your time and effort in point me in the right direction.
I will give it a try today and will report back.

Thank you have a great day