Conditional Formatting > 3 Conditions

[Dickie Worton]

Hello Again!

Can anyone help me at all, please?

Having used this discussion group to get some terrific results using VBA I
have now encountered a problem.

I have been using a script (sample below) to get past the 3 conditions
problem with conditional formatting and have also been able to get the cells
into which data is entered to adopt the same colour as various pairs of
references in a range where upper and lower limit parameter values are held
(hope that makes sense).

I have had cause to add some additional rows of data beyond those which I
originally defined, and as such have simply amended the VBA code so that the
Target Range includes some additional cell addresses.

However, when I enter data into cells in these additional rows (57 & 58)
they do not change colour. In addition, when I delete the value from the cell
I get a Run Time Error '13': message - Type Mismatch. When select 'Debug' it
always goes to the same place in the script where the error supposedly
occurs, however I can't see anything wrong, or at least I can't see anything
different in the script from how it was when it worked.

Finally, the problem doesn't seem confined to the rows I have added in, as
changing values in any of the cells previously coloured successfully by the
script gives me the same errors, i.e. it doesn't colour the cell and deleting
the value gives me a Run Time Error.

What have I done wrong? Can anyone help at all, please? I am so exasperated,
this script seemed like the answer to my prayers but has stopped working and
I am at a loss to know how to correct it.

Any help gratefully received.

Thanks,

Dickie

NB - This is a selection from the script, it simply repeats itself for each
pair of columns, with the error always appearing to occur in the first
instance of the pairs of Case Ranges (in this instance F2 to G2).

Dim iColor As Integer

If Not Intersect(Target, Range("F19:G100")) Is Nothing Then


Select Case Target
Case Range("F2").Value To Range("G2").Value
iColor = Cells(2, 4).Interior.ColorIndex
Case Range("F3").Value To Range("G3").Value
iColor = Cells(3, 4).Interior.ColorIndex
Case Range("F4").Value To Range("G4").Value
iColor = Cells(4, 4).Interior.ColorIndex
Case Range("F5").Value To Range("G5").Value
iColor = Cells(5, 4).Interior.ColorIndex
Case Range("F6").Value To Range("G6").Value
iColor = Cells(6, 4).Interior.ColorIndex
Case Range("F7").Value To Range("G7").Value
iColor = Cells(7, 4).Interior.ColorIndex
Case Range("F8").Value To Range("G8").Value
iColor = Cells(8, 4).Interior.ColorIndex
Case Range("F9").Value To Range("G9").Value
iColor = Cells(9, 4).Interior.ColorIndex
Case Range("F10").Value To Range("G10").Value
iColor = Cells(10, 4).Interior.ColorIndex
Case Else
End Select
Target.Interior.ColorIndex = iColor
End If

 

[Bernie Deitrick]

Dickie,

Try the one liner:

icolor = Cells(Application.WorksheetFunction.Match(Target.Value, Range("F:F")),
4).Interior.ColorIndex

HTH,
Bernie
MS Excel MVP

 

[Dickie Worton]

Hi Bernie,
Thanks for responding, sorry for the delay in trying this out but I think
time differences have been a factor!

OK, I'm sorry but I need to ask whereabouts I should insert your suggested
one-liner into my existing script, I'm still pretty new to VBA programming
and need this spelled out pretty simply if I'm not to mess up.

Regards,
Dickie

 

[Bernie Deitrick]

Dickie,

Comment out the entire select case block of code (And here's a VBE tip, in case you don't know how
to comment out lots of code quickly (since you're new): in the VBE, select View, then Toolbars, and
make sure that Edit is checked. Then select the block of code, and click the "Comment Block"
button. You can hover your mouse cursor over each of the buttons to learn what it is called... And,
of course, there is an "Uncomment Block" button, too.)

Comment out from

Select Case Target

to

End Select

and put the line of code that I suggested just before you set the colorindex:

icolor = Cells(Application.WorksheetFunction.Match( _
Target.Value, Range("F:F")), 4).Interior.ColorIndex
Target.Interior.ColorIndex = iColor

Of course, if it works, you could combine these into one line:

Target.Interior.ColorIndex = Cells(Application.WorksheetFunction.Match( _
Target.Value, Range("F:F")), 4).Interior.ColorIndex

HTH,
Bernie
MS Excel MVP