Formating Cells used code when worksheet opened
 

Hello, I'm passing data into an existing excel file using access. I want to
format the cells in the worksheet based on the text in each cell.

For Example:
Cell = Henry Smith*1
Cell = Henry Smith*3
Cell = Henry Smith*2
Cell = Henry Smith*3
Cell = Henry Smith*4
Cell = Henry Smith*5
Cell = Henry Smith*2

I want when the cell has *1 in it to set the color = red, when then *2 set
the cell color = blue, *3 set the cell color = yellow.

Is this possible.

Thanks

Don

Formating Cells used code when worksheet opened
 

you need to use some vba to set the cell color.

basically the process is

sub setcellcolor(r as range)

dim lcolorindex as long
select case right(r.value,2)
case "*1": lColorIndex = 3 ' red
case "*2": lColorIndex = 5 ' blue
case "*3":lColorIndex = 6 ' yellow
case else: lColorIndex = 0 ' nothing
end select
if lcolorindex 0 then
with r.Interior
.ColorIndex = lcolorindex
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
endif

end sub

'and you can call it with

sub setcolors
dim r as range
for each r in activesheet.usedrange
setcellcolor(r)
next r
end sub

hope this gets you started.
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Don" wrote:

Hello, I'm passing data into an existing excel file using access. I want to
format the cells in the worksheet based on the text in each cell.

For Example:
Cell = Henry Smith*1
Cell = Henry Smith*3
Cell = Henry Smith*2
Cell = Henry Smith*3
Cell = Henry Smith*4
Cell = Henry Smith*5
Cell = Henry Smith*2

I want when the cell has *1 in it to set the color = red, when then *2 set
the cell color = blue, *3 set the cell color = yellow.

Is this possible.

Thanks

Don

Formating Cells used code when worksheet opened
 

You can do this with Format = Conditional Formatting.

Example: Your format for A1 would look like this:
Condition1: Formula is.... =FIND("*1",A1) Format:red color
Condition2: Formula is.... =FIND("*2",A1) Format:bluecolor
etc.

Cheers,
Joerg Mochikun



"Don" wrote in message
...
Hello, I'm passing data into an existing excel file using access. I want
to
format the cells in the worksheet based on the text in each cell.

For Example:
Cell = Henry Smith*1
Cell = Henry Smith*3
Cell = Henry Smith*2
Cell = Henry Smith*3
Cell = Henry Smith*4
Cell = Henry Smith*5
Cell = Henry Smith*2

I want when the cell has *1 in it to set the color = red, when then *2 set
the cell color = blue, *3 set the cell color = yellow.

Is this possible.

Thanks

Don