excel - Use VBA union function to conditionally add cells to range -

i trying add cells respective ranges depending on interior colour using union method. code using follows:

for each cell in entirepossiblerange      if cell.interior.color = rgb(132, 151, 176)         set accessabilityrange = union(accessabilityrange, range("b" & cell.row)     else if cell.interior.color = rgb(244, 176, 132)         set consistencyrange = union(consistencyrange, range("b" & cell.row)     else if cell.interior.color = rgb(255, 217, 102)         set efficacyrange = union(efficacyrange, range("b" & cell.row)     else cell.interior.color = rgb(191, 191, 191)         set widerimpactsrange = union(widerimpactsrange, range("b" & cell.row)     end if   next cell  end sub 

however code won't run , throws syntax error. cannot figure out syntax error though.

you can add separate function avoid repeating same code checks each time:

function joinranges(r1 range, r2 range) range     if r1 nothing         set joinranges = r2     else         if r2 nothing             set joinranges = r1         else             set joinranges = union(r1, r2)         end if     end if end function 

then code becomes (i'd use select case ;)):

for each cell in entirepossiblerange      select case cell.interior.color     case rgb(132, 151, 176)         set accessabilityrange = joinranges(accessabilityrange, range("b" & cell.row))     case rgb(244, 176, 132)         set consistencyrange = joinranges(consistencyrange, range("b" & cell.row))     case rgb(255, 217, 102)         set efficacyrange = joinranges(efficacyrange, range("b" & cell.row))     case rgb(191, 191, 191)         set widerimpactsrange = joinranges(widerimpactsrange, range("b" & cell.row))     end select   next cell