MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Excel 2000 Conditional formatting

  Asked By: Anita    Date: Sep 30    Category: MS Office    Views: 1373

I'm trying to conditional format a cell in code.

sgR & sgC = Language specific characters for Row and Column... R/R C/K
and so on.
cgDQ = a double quote.

The code for the formula is... Active Cell = D6

ActiveCell.Offset(0, 6).FormulaR1C1 = _
"=if(" & sgR & sgC & "[-2]=" & sgR & sgC & "[-1]" _
& "," & cgDQ & cgDQ _
& "," & sgR & sgC & "[-2] -" & sgR & sgC & "[-1])"

Resulting in... (example)
=IF(H6=I6,"",H6 -I6)


With ActiveCell.Offset(0, 6)
.FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlNotEqual, _
Formula1:=cgDQ & cgDQ
.FormatConditions(1).Font.ColorIndex = 3
.FormatConditions(1).Interior.ColorIndex = 6
End With

What I'm trying is in that if the cell is not = "" then set the font
to red and the background of the cell to yellow. After experimenting I
know that this shows up nicely on a monochrome printout.

What I'm getting when I6=0 and H6=0 ... for example ... A yellow
highlight in the cell that I don't think should be there.



17 Answers Found

Answer #1    Answered By: Garritt Bakker     Answered On: Sep 30

I see your difficulty.
Your '=IF(H6=I6,"",H6-I6)' formula forces the length of the cell value
to 0 if there is no difference, so by only applying formatting  when
the length is non-zero (ie. TRUE), I can get round it thus:

With ActiveCell.Offset(0, 6)
.FormatConditions.Add _
Type:=xlExpression, _
.FormatConditions(1).Font.ColorIndex = 3
.FormatConditions(1).Interior.ColorIndex = 6
End With
but I see a problem:
Since you want to handle international settings and my solution above
includes a formula as a string expression, this may not carry across
well. I may be having a senior moment, I can't think of an easier way
to refer to the cell in which the conditional  formatting resides other

Answer #2    Answered By: Cameron Smith     Answered On: Sep 30

easier perhaps to replace
Formula1:="=len(" & .Address & ")"
so now you only have to deal with the international aspect of 'len'.

BTW, I notice that repeating the format condition setting code adds a
new condition every time.. it might be worth inserting a
before adding another.

Answer #3    Answered By: Akina Suzuki     Answered On: Sep 30

Yeah... I put the delete in a while back just after I posted the

Answer #4    Answered By: Abraham Lopez     Answered On: Sep 30

To refer to the current cell I sometimes use OFFSET and use A1 as the
start point.



Will sum the contents of column D from D5 to two rows above the cell
containing the formula.
I do this because of inserting rows. The offset makes *certain* of the
range being summed

Answer #5    Answered By: Eula Armstrong     Answered On: Sep 30

my "interim" solution till I got an answer from the
masters & mistresses on this list :-) was to change the formula to
give a result... delete the IF.. and when the value was zero set the
font to white.

Answer #6    Answered By: Samuel Evans     Answered On: Sep 30

Unfortunately it didn't work. Am now experimenting
womanually to try and get what I want... which is.. if the cell is
<>"" then red font and yellow background.

.. but I'm of the same opinion as you and Dave that it's the test for
the contents that's doing the dastardly deed!

Will let you know.

Answer #7    Answered By: Fergus Jones     Answered On: Sep 30

Thats' very surprising. I went and tested this on a Excel 2000/win2k
machine abd it worked just fine, all solutions proffered did. Here is
the code I used, with various versions commented out:

Sub blah()
sgR = "R"
sgC = "C"
cgDQ = """"
ActiveCell.Offset(0, 6).FormulaR1C1 = _
"=if(" & sgR & sgC & "[-2]=" & sgR & sgC & "[-1]" _
& "," & cgDQ & cgDQ _
& "," & sgR & sgC & "[-2] -" & sgR & sgC & "[-1])"

'With ActiveCell.Offset(0, 6) 'Lisa's original
'.FormatConditions.Add _
'Type:=xlCellValue, _
'Operator:=xlNotEqual, _
'Formula1:=cgDQ & cgDQ
'.FormatConditions(1).Font.ColorIndex = 3
'.FormatConditions(1).Interior.ColorIndex = 6
'End With

'With ActiveCell.Offset(0, 6) 'my 1st offering
'.FormatConditions.Add _
' Type:=xlExpression, _
'.FormatConditions(1).Font.ColorIndex = 3
'.FormatConditions(1).Interior.ColorIndex = 6
'End With

'With ActiveCell.Offset(0, 6) 'my 2nd offering
'.FormatConditions.Add _
' Type:=xlExpression, _
' Formula1:="=len(" & .Address & ")"
'.FormatConditions(1).Font.ColorIndex = 3
'.FormatConditions(1).Interior.ColorIndex = 6
'End With

With ActiveCell.Offset(0, 6) 'David's solution
.FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlNotEqual, _
Formula1:="=" & cgDQ & cgDQ
.FormatConditions(1).Font.ColorIndex = 3
.FormatConditions(1).Interior.ColorIndex = 6
End With
End Sub

Answer #8    Answered By: Dashiell Jones     Answered On: Sep 30

I will "revisit" the solution you gave and give it a go.

But... I would appreciate it if you could post the condition string
from conditional  formatting that is generated that works.

We are on the same page here right?
If the cell contains "" from the IF, then the cell looks nomal...
white background and black text but there isn't any text.
If the cell contains anything else then the font is red and the
background is yellow.

Answer #9    Answered By: Emily Campbell     Answered On: Sep 30

Cell Value is | Not equal to

my 2nd offering:
Formula is

my 1st offering:
Formula is

Try running the code below by itself in a fresh spreadsheet and look
at the conditional  formatting in J6 and play with the values in H6 and
I6. They all work (we are on the same page).
Note also what cgDQ contains.

Answer #10    Answered By: Brooke Robertson     Answered On: Sep 30

And 'tis okay ... I use the following to check language and set some
globals I for that stuff.

Sub subLangSettings()
' Set some constants dependent on Language.

sgEMsg = ""
Select Case Application.LanguageSettings.LanguageID(msoLanguageIDUI)
Case 1033 ' US
sgR = "R"
sgC = "C"
sgColumn = "Column"
sgRow = "Row"
sgOffset = "Offset"
sgSum = "Sum"
Case 1043 ' Dutch
sgR = "R"
sgC = "C"
sgColumn = "Kolom"
sgRow = "Rij"
sgSum = "Sum"
Case Else
sgEMsg = "Unknown Language setting."
End Select

Answer #11    Answered By: Trina King     Answered On: Sep 30

If you look at the actual conditional  format inserted, you'll see that the thing
it's looking for is


I.e. it's testing for two actual consecutive quotes and will highlight
everything that isn't a pair of quotes. In fact, you want it to be


To achieve this, you need to include an equals sign in the formula you give it

Formula1:="=" & CGDQ & CGDQ

Answer #12    Answered By: Baden Smith     Answered On: Sep 30

The four quotes was what Excel converted two quotes to. Using my own
constants doesn't do anything to change that. If I try manually
setting a condition to 2 quotes it gets converted to ="""" every time.

Answer #13    Answered By: Zeke Thompson     Answered On: Sep 30


You need to set the formula to equal sign quote quote i.e. ="".

Hence the "=" & in my version of the statement.

Just copy/paste my statement into your code. It worked fine in Excel 2003.

Answer #14    Answered By: Amir Hashmi     Answered On: Sep 30

With respect... It don't seem to work in Excel 2000.

If you could try it in that environment I would appreciate it so I
know wether or not I'm doing something wrong.

Answer #15    Answered By: Jarryd Williams     Answered On: Sep 30

I tested in Excel 2000, did you try copy/pasting the macro I

Answer #16    Answered By: Kate Johnston     Answered On: Sep 30

I just inserted a new sheet, copied the code and ran it.

It worked.

I think the "major" difference there that I had Type:=xlCellValue and
you had Type:=xlExpression.

Davids solution also worked.

He included an = in front of the 2 quotes.

I blame me just being careless..

Answer #17    Answered By: Gavril Bonkob     Answered On: Sep 30

Just tried it in Excel 2000 and it works fine.

Post your code and I'll check it against mine.

Didn't find what you were looking for? Find more on Excel 2000 Conditional formatting Or get search suggestion and latest updates.