Formating Cells used code when worksheet opened

[Don]

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

 

[Guest]

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.

 

[Joerg]

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