PDA

View Full Version : Solved: Color Coding Specific Cell Value



SUJU
10-08-2005, 11:14 PM
Hi
I used some of the replies in the archives, but am still encountering problems. Please tell me where am i going wrong. I need to color code specific cells values in Col C from Row 5 downwards.
If value is >=14 and <= 22 color code the cell color 4.
If value is >= 35 then color code 8,
else, leave the color as blank

Thanks

Sub colorcell()
Dim r As Integer
Dim c As Range

'r is the count of the rows
'c is the Column C

Range("C1").Select

For r = 1 To 250
If Cells(r, c) = "" Then
Cells(r, c).Interior.ColorIndex = 0
ElseIf Cells(r, c) > 14 And Cells(r, c) < 22 Then
Cells(r, c).Interior.ColorIndex = 4
ElseIf Cells(r, c) > 22 And Cells(r, c) < 35 Then
Cells(r, c).Interior.ColorIndex = 0
Else
Cells(r, c).interior.ColorIndex = 0
End If
Next c
Next r
End Sub

jindon
10-08-2005, 11:47 PM
Hi


If Cells(r, c)
r is defined as integer and c is range.

What do you want to do?

johnske
10-09-2005, 12:45 AM
Hi SUJU,

Is this what you mean to do? (This is a faster way to use a loop)Option Explicit
Sub colorcell()
Dim cel As Range
For Each cel In Range("C5:C250")
If cel >= 14 And cel <= 22 Then
cel.Interior.ColorIndex = 4
ElseIf cel >= 35 Then
cel.Interior.ColorIndex = 8
Else
cel.Interior.ColorIndex = 0
End If
Next cel
End Sub
..... I need to color code specific cells values in Col C from Row 5 downwards.
If value is >=14 and <= 22 color code the cell color 4.
If value is >= 35 then color code 8,
else, leave the color as blankHTH,
John

SUJU
10-09-2005, 01:23 AM
HI Johnske
Thanx ...thats exactly what i needed. Just have two more questions regarding this. Because my spreadsheet is quite big, i have used cond formating to color every alternate row...How do I make that color subordinate to the individual cell color that you have coded.
Secondly, Will the cells get uncolored if the cell values change?

Thanx Suju

johnske
10-09-2005, 03:23 AM
You'll have to run the procedure again to change the colors if the values change, so you'd probably be best doing this as a worksheet event (Worksheet_Change) as in the example below.

I don't know of a way to 'subordinate' this as you ask (other than by deleting any conditional format), you'd probably be best to add all the conditions you have for conditional formatting to your code. But it sounds like you need conditional formatting for more than 3 conditions - correct? There's a KB entry by DRJ that does this here > http://www.vbaexpress.com/kb/getarticle.php?kb_id=90
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Range("C5:C250")
If cell >= 14 And cell <= 22 Then
cell.Interior.ColorIndex = 4
ElseIf cell >= 35 Then
cell.Interior.ColorIndex = 8
'------------------------------------
'add any additional conditions below
ElseIf cell = 5 Then
cell.Interior.ColorIndex = 6
ElseIf cell = 30 Then
cell.Interior.ColorIndex = 3
'------------------------------------
Else
cell.Interior.ColorIndex = xlNone
End If
Next cell
End Sub

royUK
10-09-2005, 05:33 AM
Cross post:

http://www.ozgrid.com/forum/showthread.php?t=40667

Bob Phillips
10-09-2005, 06:46 AM
i have used cond formating to color every alternate row...How do I make that color subordinate to the individual cell color that you have coded.

You can't, conditional formatting is superior to manual formatting.

..

SUJU
10-09-2005, 08:29 PM
Thanks all

xCav8r
10-09-2005, 08:36 PM
SUJU, if this is solved, please use the link Thread Tools at the top of this page to mark your thread solved. :)

Note: I edited the thread title to reduce the ALL CAPS to Title Case.