Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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)

And...

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.

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, _

Formula1:="=LEN(INDIRECT(ADDRESS(ROW(),COLUMN())))"

.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

than INDIRECT(ADDRESS(ROW(),COLUMN())).

easier perhaps to replace

Formula1:="=LEN(INDIRECT(ADDRESS(ROW(),COLUMN())))"

with

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

.FormatConditions.Delete

before adding another.

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

question.

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

start point.

OFFSET(reference,rows,cols,height,width)

=SUM(D5:OFFSET(A1,ROW()-2,COLUMN()-1))

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

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.

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.

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 = """"

Range("D6").Select

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.Delete

'.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.Delete

'.FormatConditions.Add _

' Type:=xlExpression, _

' Formula1:="=LEN(INDIRECT(ADDRESS(ROW(),COLUMN())))"

'.FormatConditions(1).Font.ColorIndex = 3

'.FormatConditions(1).Interior.ColorIndex = 6

'End With

'With ActiveCell.Offset(0, 6) 'my 2nd offering

'.FormatConditions.Delete

'.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.Delete

.FormatConditions.Add _

Type:=xlCellValue, _

Operator:=xlNotEqual, _

Formula1:="=" & cgDQ & cgDQ

.FormatConditions(1).Font.ColorIndex = 3

.FormatConditions(1).Interior.ColorIndex = 6

End With

End Sub

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.

Cell Value is | Not equal to

=""

my 2nd offering:

Formula is

=LEN($J$6)

my 1st offering:

Formula is

=LEN(INDIRECT(ADDRESS(ROW(),COLUMN())))

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.

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"

sgOffset = "VERSCHUIVING"

sgSum = "Sum"

Case Else

sgEMsg = "Unknown Language setting."

End Select

'

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

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.

Correct.

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.

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.

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

sent?

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..

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.

Related Topics:

- Excel 2007 conditional formatting
- Excel VBA Problem in excel 2000
- Convert code from excel 2000 vba to excel 2003 vba
- VBA Function in Excel 2000
- Excel 2000: Time totaling variable 'overflows' at 24:00 and display
- Excel 2000 Error 1004
- Is there a way to have an input form address a hidden worksheet in excel 2000
- Excel 2000: Modifying the date with DTPicker does not fire AfterUpd
- ms excel 2000 questions
- excel 2000 question
- excel 2000 - Delete confirmation
- Excel 2000 - Names
- Excel 2000 - Copy Sheet
- Excel 2000 - Select
- excel 2000 - Wierd Paste Problem
- Excel 2000 - button on sheet
- excel 2000 - EntireColumn
- Excel 2000 - One place for code
- Excel 2000 - FreezPanes
- Excel 2000 Picking up sheet name
- Excel 2000 Sumif/Countif
- Conditional formatting?
- Conditional Formatting.
- Conditional Formatting
- chart conditional formatting