Changing Cell Colour based on the values in a different range of cells

[EliseFreedman]

Hi There

I am working on a spreadsheet which is going to have a scoring system behind it.

Basically what will happen is that users will select values for a range of indicators. If ANY of the values in the range are Red, then the overall score should be red. I tried to use the code below but it didnt seem to be working. Im not sure where I am going wrong.

Dim NxtRow

For NxtRow = 7 To 15
    If Range("G" & NxtRow).Interior.ColorIndex = 45 Then
        Range("G16").Interior.ColorIndex = 45
        Else
         Range("G16").Interior.ColorIndex = 0
    End If
Next

Can anyone help

 

[SkipVought]

Hi,

So the overall score is in row 16?

Dim NxtRow as long, r as range, ci

For NxtRow = 7 To 15
   for each r in interesect(rows(NxtRow), ActiveSheet.UsedRange) 
      ci = r.interior.colorindex
      select case ci
         case xlNone
            ci = xlNone
      end select
      cells(16, r.column).interior.colorindex = ci
   next
Next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[yimitz]

I thought the color index for red was 3 instead of 45.